CODEWITHSUNDEEP
excelvba
user2623878
user2623878

Reputation: 11

Using an Or function within Range.Find VBA

Is it possible to put and OR function within a range.find? I have this function but I'd like it to look for either of 2 values

With Worksheets("Term").Range("A:A")

    Set rng = .Find(What:=Worksheets("Term and Discipline").Range("K7"), _
        After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)
        Application.Goto rng, True

End With

In the line where you provide what it's looking for I've tried to put in an OR statement but Excel gets mad at me when I try to run it

Upvotes: 1

Views: 9983

Answers (3)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

You need to loop through all the cells in your range and use the Like function or InStr in an If statement with Or.

Upvotes: 0

Andy G
Andy G

Reputation: 19367

I suppose the nearest equivalent would be to perform the searches in parallel (effectively) and use MIN() to select the first cell found.

Sub FindingNemor()
    Dim rngFoo As Range
    Dim rngBar As Range

    Set rngFoo = Cells.Find(What:="foo", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    Set rngBar = Cells.Find(What:="bar", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    If Not rngFoo Is Nothing And Not rngBar Is Nothing Then
        Range("A1").Cells(Application.Min(rngFoo.Row, rngBar.Row)).Select
     End If
End Sub

It requires extra checks in case only one of rngFoo or rngBar is Nothing.

Added Checking for Nothing-ness makes it a little messier:

Sub FindingNemor()
    Dim rngFoo As Range
    Dim rngBar As Range

    Set rngFoo = Cells.Find(What:="foo", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    Set rngBar = Cells.Find(What:="bar", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    If Not rngFoo Is Nothing And Not rngBar Is Nothing Then
        Range("A1").Cells(Application.Min(rngFoo.Row, rngBar.Row)).Select
    ElseIf rngFoo Is Nothing Then
        If rngBar Is Nothing Then
            MsgBox "Neither found."
        Else
            Range("A1").Cells(rngBar.Row).Select
        End If
    Else
        Range("A1").Cells(rngFoo.Row).Select
    End If
End Sub

Upvotes: 3

Joseph
Joseph

Reputation: 5160

The Range.Find method emulates the same window as when you type CTRL+F in Excel. Since you can't search for 2+ values at the same time in that window, I don't believe you can search for 2+ values at the same time through VBA. I think you would have to do the method twice, unfortunately.

If I'm wrong, I would love to know how to do this; it would be so useful. So please feel free to prove me wrong :)

From my testing, the Range.Find method does not support arrays or a range of more than 1 cell. So those are out.

Also, trying to use OR would not work correctly because OR checks for TRUE/FALSE and returns TRUE/FALSE.

Upvotes: 2

Related Questions