Check if item is in a list in an Excel macro

Wondering if there is way using an Excel macro to check if an item is in a list instead of using IF THEN ELSE statements.   As an example, let's say of the 50 United States, if a particular state is a blue state, do something; if a particular state is a red state, do something else.  I'm trying to avoid coding something like what I have below, which is tedious and probably also takes a lot longer to process (especially if dealing with a large number of items that need to be checked) than some other method:

If state = 'CA' or 'NY' or 'NJ' or 'NH' or ......etc, etc  then
do something
elseif state = 'TX' or 'SC' or 'GA' or.... etc etc then
do something
else                       <------ it's possible in the source data that a particular item, for some reason, is not a state at all
do something
Who is Participating?
Glenn RayExcel VBA DeveloperCommented:
@Rgonzo1971.... I've never seen the Filter method used like that.  Very cool.  (I'm saving this thread for future reference).

Here's an example of the Select...Case method (you'd expand the state lists, as needed):
Sub Select_States()
    Dim strState As String
    strState = Range("A2").Value 'example assignment
    Select Case strState
        Case "CA", "NY", "NJ", "NH"
            'Blue state processing
        Case "TX", "SC", "GA"
            'Red state processing
        Case "FL", "OH", "IA"
            'flip-flop states
        Case Else
            'handles all other values not handled above
    End Select
End Sub

Open in new window


you could use select case

you could also try filter

Sub Macro1()

arr1 = Array("aa", "bb")
arr2 = Array("cc", "dd")

' filter gives an array of variant back
If UBound(Filter(arr1, "dd")) >= 0 Then
    ' Your code
ElseIf UBound(Filter(arr2, "dd")) >= 0 Then
    ' Your code
    ' Your code
End If

End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.