• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

VBA - Finad Names, replace Value & Format

Hi Experts,

I need some help with the attached Excel file.  You can see the code I have for FindNames.  This should look at each name in Column G in "Macro Completed" sheet, and if that name is listed in "Names" sheet, it should replace value in Column E of "Macro Completed" with value next to name on Names Sheet (ST Name) as well as perform some formatting, etc.  Right now it only does it for the first instance of each name found.  I need it to work for entire sheet.

THanks!
Cody-
Macro-Sample.xls
0
Cody Vance
Asked:
Cody Vance
  • 2
1 Solution
 
StephenJRCommented:
Cody - see if this works:
Public Sub FindNames()

Dim wkb As Workbook
Dim dataSht As Worksheet
Dim namesSht As Worksheet
Dim rng As Range
Dim fRange As Range
Dim myName As Range
Dim s As String

Set wkb = ThisWorkbook
Set namesSht = wkb.Sheets("Names")
Set dataSht = wkb.Sheets("Macro Completed")

For Each myName In namesSht.Range("A2", namesSht.Range("A" & namesSht.Rows.Count).End(xlUp)) 'First name to search for in Column A
    If myName <> "" Then
        Set fRange = dataSht.Range("G:G").Find(What:=myName.Value, LookIn:=xlFormulas, LookAt:=xlWhole) 'Search in Column G for "Name" from column A
        If Not fRange Is Nothing Then 'Name found!
            'myName.Font.Bold = True 'Make Column A name Bold as in example code
            'myName.Interior.ColorIndex = 15 'Color Code Column A
            s = fRange.Address
            Do
                fRange.Offset(0, -6).Font.Bold = True
                fRange.Offset(0, -6).Interior.ColorIndex = 15
                fRange.Offset(0, -2).Value = myName.Offset(0, 1).Value 'set Column E same row = column B from myName match
                fRange.Offset(0, -2).Interior.ColorIndex = 15 'color code Column E
                fRange.Offset(0, -1).Value = "Please Send to Adam Farer for check in." 'alert message Column F
                fRange.Offset(0, -1).Font.Bold = True 'Bold Column F as in example code
                Set fRange = dataSht.Cells.FindNext(fRange)
            Loop While fRange.Address <> s
        End If
    End If
    s = ""
Next myName

End Sub

Open in new window

0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Awesome!  Works perfectly.   Thanks for Speedy Response!

Cody-
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Guess I jumped the gun, getting Application object defined error on

fRange.Offset(0, -6).Font.Bold = True
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now