Can the Excel VBA Range.Find method be used to find multiple values?-CodePudding

I'm attempting to search for text located in an Excel spreadsheet. I was looking at the examples for the Range.Find method located in Microsoft docs: https://docs.microsoft.com/en-us/office/vba/api/excel.range.find.

These examples only show how to search for one value. Is it possible to search for more than one value using the Range.Find method or would one have to place multiple Range.Find methods inside a nested For loop or some other VBA structure? Or would it be more efficient to use a Pandas structure? I'd like some expert advice on how to implement this.

Thanks in advance.

CodePudding user response：

Yes, you can do repeated Range.Find calls and use the After argument to make sure the next call doesn't re-find the previous range. VBA actually has a built in function to help simplify this proces, Range.FindNext

So you first set up a Range.Find and then VBA saves the arguments so you can do Range.FindNext and it will duplicate your arguments from the .Find Function. You only need to specify the After argument in Range.FindNext.

Here is an example and a function for how to set this up:

Sub Example2()
    Dim relevantRanges(2) As Range
    Set relevantRanges(0) = FindAll("unopened")
    Set relevantRanges(1) = FindAll("needs work")
    Set relevantRanges(2) = FindAll("incomplete")
    'Now each element in relevantRanges contains a range of all found cells with it's word
End Sub

Function FindAll( _
                    LookFor As String, _
                    Optional WithinSheet As Worksheet = Nothing, _
                    Optional LookIn As XlFindLookIn = xlValues, _
                    Optional LookAt As XlLookAt = xlPart, _
                    Optional MatchCase As Boolean = False _
                ) As Range
    
    If WithinSheet Is Nothing Then Set WithinSheet = ActiveSheet
    
    With WithinSheet.Cells
        Dim rFirst As Range
        Set rFirst = .Find(What:=LookFor, LookIn:=LookIn, LookAt:=LookAt, MatchCase:=MatchCase)
                
        If rFirst Is Nothing Then
            Set FindAll = Nothing
            Exit Function
        End If

        Dim FoundRange As Range
        Set FoundRange = rFirst
        
        Dim rNext As Range
        Set rNext = .FindNext(After:=rFirst)
        
        While rNext.Address <> rFirst.Address
            Set FoundRange = Union(FoundRange, rNext)
            Set rNext = .FindNext(After:=rNext)
        Wend
    End With
    
    Set FindAll = FoundRange
End Function

The reason I use rFirst and rNext as separate ranges is so I have an exit condition for the loop. I can compare the addresses to make sure that when .Find returns back to the first match, it exits the While loop.