Multple find requests in vba Excel (Find within a Find)

0
votes

I am trying to perform a type of nested find request, the use case is that I need to look for a group on one worksheet, if found take the user ID value from a seperate column in the found row and then search for that ID in another sheet. It should then perform a bunch of actions and then find the next occurance of group in the first sheet.

The code I have is 

LookupGroup = Split("GroupName1,GroupName2", ",")
For I = 0 To UBound(LookupGroup)
    With Worksheets("RawData").Range("C:C")
        Set C = .Find(LookupGroup(I), LookIn:=xlValues)
        If Not C Is Nothing Then
            FirstAddress = C.Address
            Do
                LookupId = Sheets("RawData").Cells(C.Row, 7).Value
                IdExist = False
                'Check to ensure ID does not exists on Team Members Tab
                Set IdRange = Sheets("Team Members").Range("A:A").Find(LookupId, LookIn:=xlValues)
                If IdRange Is Nothing Then
                    IdExist = True
                End If
                If Not IdExist Then
                    Highlight = True 'trigger to Set row to bold red font
                    If RecordsFound > 0 Then
                        TotalRecords = TotalRecords + RecordsFound
                    End If
                End If
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> FirstAddress
        End If
    End With
Next I

This works fine the first time through, however upon reaching the Set C = .FindNext(C) the command returns 'Nothing' rather than the next occurence.

If I comment out the second find 

Set IdRange = Sheets("Team Members").Range("A:A").Find(LookupId, LookIn:=xlValues)

Then the first search works fine and finds all instances

What am I doing wrong?

2
vbaexcel
You can't nest two separate Find()'s like that. One solution would be to first find all of the "Group" cells, put them in an array or collection, then loop through the collection and do the Team Member lookup. Or use Match() in the inner loop instead of Find – Tim Williams

2 Answers

5
votes

Easier to take the Find() logic and put it in a separate function...

Sub Tester()
Dim LookupGroup, rngGrp As Range, rngMember As Range, I
Dim g As Range, m As Range

    LookupGroup = Split("GroupName1,GroupName2", ",")

    For I = 0 To UBound(LookupGroup)

        Set rngGrp = FindAll(Worksheets("RawData").Range("C:C"), LookupGroup(I))

        If Not rngGrp Is Nothing Then
            For Each g In rngGrp.Cells

                Set rngMember = FindAll(Sheets("Team Members").Range("A:A"), _
                                        g.EntireRow.Cells(7))

                If Not rngMember Is Nothing Then
                    For Each m In rngMember.Cells
                        'do something with m
                    Next m
                Else
                    'flag not found...
                End If
            Next g
        End If
    Next I

End Sub

'find all matching cells in a given range
Function FindAll(rngLookIn As Range, LookFor) As Range

    Dim rv As Range, c As Range, FirstAddress As String
    With rngLookIn
        Set c = .Find(LookFor, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Set rv = c
            Do
                Set c = .FindNext(c)
                If Not c Is Nothing Then Set rv = Application.Union(rv, c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    Set FindAll = rv
End Function
0
votes

I know it is an old question but it can do what you want replacing FindNext with another search but in a limited range, not the entire column "C". First find the last row of "C" with the LastRow function and use Find with Worksheets("RawData").Range("C1:C" & LRow). At the end, instead of using FindNext, use Find again with Range("C" & C.Row + 1 & ":C" & LRow)

Public Function LastRow(ByRef wsSheet_I As Worksheet, ByVal lColumn_I As Long) As Long
    Dim LRow As Range

    Set LRow = wsSheet_I.Columns(lColumn_I).Find( _
        What:="*", _
        LookIn:=xlFormulas, _
        Lookat:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
    
    If Not LRow Is Nothing Then
        LastRow = LRow.Row
    Else
        LastRow = 0
    End If
End Function

Public Sub FindInFind()
LookupGroup = Split("GroupName1,GroupName2", ",")
For i = 0 To UBound(LookupGroup)
'new code
    Dim LRow As Long
    LRow = LastRow(Worksheets("RawData"), 3)
    If LRow = 0 Then GoTo ErrorHandling
        Dim C As Range
        Set C = Worksheets("RawData").Range("C1:C" & LRow).Find(LookupGroup(i), LookIn:=xlValues)
'end new code
    'With Worksheets("RawData").Range("C:C")
        'Set C = .Find(LookupGroup(i), LookIn:=xlValues)
        If Not C Is Nothing Then
            'FirstAddress = C.Address
            Do
                LookupId = Sheets("RawData").Cells(C.Row, 7).Value
                IdExist = False
                'Check to ensure ID does not exists on Team Members Tab
                Set IdRange = Sheets("Team Members").Range("A:A").Find(LookupId, LookIn:=xlValues)
                If IdRange Is Nothing Then
                    IdExist = True
                End If
                If Not IdExist Then
                    Highlight = True 'trigger to Set row to bold red font
                    If RecordsFound > 0 Then
                        TotalRecords = TotalRecords + RecordsFound
                    End If
                End If
                'Set C = .FindNext(C)
'new code
                Set C = Worksheets("RawData").Range("C" & C.Row + 1 & ":C" & LRow) _
                  .Find(LookupGroup(i), LookIn:=xlValues)
'end new code
            Loop While Not C Is Nothing 'And C.Address <> FirstAddress
        End If
    End With
Next i

'new code
Exit Sub
ErrorHandling:
'do something with the error
'end new code
End Sub