Learn how to a build a cloud-first strategyRegister Now


Check if item is in a list in an Excel macro

Posted on 2014-08-27
Medium Priority
Last Modified: 2014-09-03
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
Question by:dbfromnewjersey
LVL 54

Assisted Solution

Rgonzo1971 earned 1000 total points
ID: 40287807

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

LVL 27

Accepted Solution

Glenn Ray earned 1000 total points
ID: 40288641
@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


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question