CODEWITHSUNDEEP
vbaexcel
Vinicius
Vinicius

Reputation: 558

Is there an easy way to selecting multiple criteria in vba?

I want to check if a cell is equal to some elements, like the example below.

If Range("A1") = "Element1" Or Range("A1") = "Element2" Or Range("A1") = "Element3" Or Range("A1") = "Element4" Or Range("A1") = "Element5" Then 
 'Do the code
End if

I think this way is a little confusion to read, especially because the real code that I'm making will have 35 elements.

I know that in MySql can use the command IN like the example below.

subject IN ('Cookery','Chemistry','Literature')

Is there something like that in vba?

Upvotes: 1

Views: 36

Answers (1)

David Zemens
David Zemens

Reputation: 53663

A Case switch is probably best-suited for this:

Select Case Range("A1").Value
    Case "Element1", "Element2", "Element3", "Element4", "Element5"
        'Do stuff

    Case Else
         'Do other stuff, or do nothing at all

End Select

Note that the above is case-sensitive. If you have mixed-case and don't care about capitalization, convert to a single case using the LCASE or UCASE function:

Select Case LCase(Range("A1").Value))
    Case "element1","element2","element3","element4","element5"

Alternatively, you can use the filter function on a vector array:

Const Elements As String = "element1,element2,element3,element4,element5"
Dim val$, myArray
myArray = Split(Elements, ",")
val = "element3"

    If UBound(Filter(myArray, val)) >= 0 Then
        'the value is in the array
        MsgBox True
    Else
        'the value is not in the array
        MsgBox False
    End If

There are some quirks with the Filter function though, and I think it is not always 100% reliable since it will return partial matches.

Or you can use the Match function against an array:

If Not IsError(Application.Match(val, myArray, False)) Then
    'value exist in the list
Else
    'value does not exist in the list
End If

Upvotes: 3

Related Questions