Excel Guides

Understanding the Select Case Structure in Excel

The Select Case structure in Excel allows you to compare a value against a list of values, and then take different actions depending on the result of the comparison. It is similar to the IF statement, but with one important difference: the Select Case statement can only test for equality, whereas the IF statement can test for a variety of conditions. This makes the Select Case statement ideal for testing whether a value falls within a certain range.

Here is the basic syntax of the Select Case statement:

Select Case testexpression 
    Case expression1 
        [statements1] 

    Case expression2 
        [statements2] 

    Case Else 
        [statements3] 
End Select

testexpression: This is the value that you want to compare against the list of values. It can be any valid expression.

expression1, expression2, ...: These are the values that you want to compare against testexpression. If testexpression is equal to one of these values, then the corresponding set of statements will be executed.

[statements1], [statements2], ...: These are the sets of statements that will be executed if < strong >testexpression is equal to < strong >expression1 , < strong > expression2 , etc. Note that these statements must be enclosed within curly braces.


Example 1: Simple Select Case Statement


Sub Example1() 

    Dim intNumber As Integer 

    intNumber = 5 

    Select Case intNumber 

        Case 1 
            MsgBox "Number is 1" 

        Case 2 
            MsgBox "Number is 2"  

        Case 3 To 10 
            MsgBox "Number is between 3 and 10"  

        Case Else  
            MsgBox "Number is not 1, 2 or between 3 and 10"  

     End Select  

 End Sub 

In this example, we have declared a variable called intNumber . We have set its value to 5.

We then use a Select Case , which tests whether intNumber . equals 1, 2 or any number between 3 and 10.

If it equals 1, then a message box appears saying "Number is 1." If it equals 2, then a message box appears saying "Number is 2." If it falls between 3 and 10 (inclusive), then a message box appears saying "Number is between 3 and 10." Otherwise (if it doesn't equal any of those values), then a message box appears saying "Number is not 1, 2 or between 3 and 10."

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.