Excel VBA Nested Finds

Question

I have a situation where I need to search for each instance where the value entered by a user (tbISearch1) appears in Column C on sheet3. For each instance found I need to take the corresponding value in Column B on sheet 3 and use these values to search sheet 2, column B and populate a listbox with the associate values in the range column A to D. So far I have the following code but I receive an error after the first loop when it tries to find the next instance for Set rngFind = .FindNext(rngFind)

With Sheet3.Range("C6:C" & lastRow)
    Set rngFind = .Find(tbISearch1, After:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlPart)
    '   If value found then set a variable for the address
    If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        '   Add the values to the listbox
        Do
            strToFind = rngFind.Offset(0, -1)
            With Sheet2.Range("B6:B" & lastRow2)
                Set rngFind2 = .Find(strToFind, After:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlPart)
                '   If value found then set a variable for the address
                If Not rngFind2 Is Nothing Then
                    '   Add the values to the listbox
                        If rngFind2.Row > 1 Then
                            lbISearch.AddItem rngFind2.Offset(0, -1)
                            lbISearch.List(lbISearch.ListCount - 1, 1) = rngFind2.Value
                            lbISearch.List(lbISearch.ListCount - 1, 2) = rngFind2.Offset(0, 1)
                            lbISearch.List(lbISearch.ListCount - 1, 3) = rngFind2.Offset(0, 2)
                        End If
                        Set rngFind2 = Nothing
                End If
            End With
            '   Find the next address to add
            Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
    End If
End With

Any help gratefully appreciated

.Find cannot nest. You need to run the first .Find to completion and make a list/table/dictionary of the matches. Then traverse the list and do the rest of the processing. Or merge the .Finds taking care to manage the starting point of the outer .Find. — AlexP
– AlexP, Commented Nov 13, 2017 at 13:53

GothicAnatomist · Accepted Answer · 2017-11-13 13:52:30Z

1

You can't call .findnext on a nested find, therefore the easiest solution is to just perform the find again, but use cell address with the 'After' parameter to get to the next found cell.

Set rngFind = .Find(rngFind, After:=Range(rngFind.Address))

answered Nov 13, 2017 at 13:52

GothicAnatomist

1564 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Nested Finds

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related