VBA Range.Find in multiple ranges... easier method?

[svendiamond]

I am trying to find a string (myLookup) in range "balRangeA"

If I don't find it there, I want to look in "balRangeB"

If I don't find it there, I want to look in "incRangeA"

Is there an easier / shorter way to manage this rather than this code I currently have:

    Dim foundIt As Range
    Set foundIt = balRangeA.Find(myLookup, , , xlWhole)
    
    If foundIt Is Nothing Then
        Set foundIt = balRangeB.Find(myLookup, , , xlWhole)
    End If
    
    If foundIt Is Nothing Then
        Set foundIt = incRangeA.Find(myLookup, , , xlWhole)
    End If

I especially want to know this because I have a lot more ranges that I need to lookup and I don't want to keep writing this IF statement.

 

[Domenic]

Try...

    Dim myRanges As Variant
    Dim myRange As Variant
    Dim foundIt As Range
    
    myRanges = Array(balRangeA, balRangeB, incRangeA)
    
    For Each myRange In myRanges
        Set foundIt = myRange.Find(myLookup, , , xlWhole)
        If Not foundIt Is Nothing Then Exit For
    Next myRange
    
    If Not foundIt Is Nothing Then
        MsgBox "The lookup value '" & myLookup & "' can be found in " & foundIt.Address(0, 0) & ".", vbInformation
    Else
        MsgBox "The lookup value '" & myLookup & "' could not be found.", vbInformation
    End If

Hope this helps!

 

[svendiamond]

Perfect! Thank you sir.