Find Name, Insert Value & Format

Hi Experts,

I received help with the attached file from other experts and just ran across an issue.  The Sub StreetTeam should find all names in the "Macro Completed" sheet Column G that are in the "Names" sheet Column A, replace the value in Column E with the ShortName from the "Names" sheet and do some simple formatting.  It works for some rows, then errors out saying apllication-defined or object-defined error.  Seems like it starts referencing the wrong lookup values?  I cant really tell.  Any help is appreciated.

Thanks,
Cody-
Sample.xls
Cody VanceSr. Analyst - ERPAsked:
Who is Participating?
 
chwong67Connect With a Mentor Commented:
Please change the findnext coomand as below:

Public Sub StreetTeam()

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:A100")
    If myName <> "" Then
        Set fRange = dataSht.Range("G1:G1500").Find(What:=myName.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not fRange Is Nothing Then
            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
                fRange.Offset(0, -2).Interior.ColorIndex = 15
                fRange.Offset(0, -1).Value = "Please Send for check in."
                fRange.Offset(0, -1).Font.Bold = True
                Set fRange = dataSht.Range("G1:G1500").FindNext(fRange)            Loop While fRange.Address <> s
        End If
    End If
    s = ""
Next myName

End Sub
0
 
chwong67Commented:
This is due to vba find cell E27 for 'Craig Surdy'.
By restrict the search range G, the4 next find cell is G27 instead of E27.
Sample-1-.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.