Check if item is in a list in an Excel macro

Posted on 2014-08-27
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 47

    Assisted Solution


    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

    @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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now