Excel BI Analytics

Tips & Tricks 174 – VBA – OR Condition in FIND

Posted on by Vijay A. Verma

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

10 thoughts on “Tips & Tricks 174 – VBA – OR Condition in FIND”

  2. Superb blog! Do you have any tips for aspiring writers?
    I'm hoping to start my own blog soon but I'm a little lost on everything.
    Would you propose starting with a free platform like WordPress or
    go for a paid option? There are so many choices out there that I'm totally overwhelmed ..
    Any recommendations? Kudos!

    Also visit my blog post … briansclub cc register

  3. Unquestionably imagine that that you said. Your favorite justification appeared to be on the net the easiest thing to bear
    in mind of. I say to you, I certainly get annoyed while other
    people consider issues that they just don't understand about.
    You controlled to hit the nail upon the top as well as defined out the whole thing without having side-effects , other people could take a
    signal. Will likely be again to get more. Thank you

    My blog post dark web bidencash

  5. https://dcshop funko.biz
    This is the perfect webpage for anyone who wants to understand this topic.
    You understand a whole lot its almost tough to argue
    with you (not that I actually would want to…HaHa).

    You certainly put a brand new spin on a subject that has
    been discussed for years. Wonderful stuff,
    just wonderful!

  6. It's aϲtually a nice аnd helpful pikece ߋf information. I am glad
    tһat you simply shared this helpful invo ᴡith սs. Ρlease keеp us uρ to
    date lіke tһіѕ. Tһanks for sharing.

    Here іs myy һomepage :: blackbets

  7. I blog frequently and I truly appreciate your information. This article has truly peaked
    my interest. I will book mark your website and keep
    checking for new details about once per week.
    I opted in for your Feed too.

    Review my website; briansclub cvv2

Comments are closed.