Tips & Tricks 174 – VBA – OR Condition in FIND

FIND is a very powerful function in VBA but it doesn't support OR condition. Hence, if you want to find say two values "A" or "B", then you can code an array within FIND. To do OR in FIND, you will need to use following code (this is a sample code only, there can be many variations of this code)

Sub FindOR()
    Dim Result As Range, Result1 As Range, Result2 As Range
    On Error Resume Next
    Set Result1 = Cells.Find("A", LookIn:=xlValues, LookAt:=xlWhole)
    Set Result2 = Cells.Find("B", LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
    Select Case True
        Case Result1 Is Nothing And Result2 Is Nothing: Exit Sub
        Case Not Result1 Is Nothing And Result2 Is Nothing: Set Result = Result1
        Case Result1 Is Nothing And Not Result2 Is Nothing: Set Result = Result2
        Case Not Result1 Is Nothing And Not Result2 Is Nothing: Set Result = Union(Result1, Result2)
    End Select
End Sub