How to use Select CASE Statement in VBA

VBA Select Case Statement Syntax with Examples
The Select-Case statement Checks a Variable or an Expression for different Cases (values). If anyone of the Case becomes true then only that Case block will be executed and the Program ignores all other Cases.

Syntax:
Select Case Condition/Variable
Case Val_1
Code to Execute When Condition = Val_1
Case Val_2
Code to Execute When Condition = Val_2
Case Val_3
Code to Execute When Condition = Val_3
Case Else
Code to Execute When all the other cases are False
End Select

Here:
'Condition’ refers to the Variable or the Expression that is to be Tested and based on which anyone of the Case blocks will be executed.

'Val_1', 'Val_2' and 'Val_3' are the possible outcomes/values of the ‘Condition’. Whenever anyone of these values matches the ‘Condition’ then its corresponding Case block will execute.

'Else' is a kind of default Case value, which will only execute when all the above Case statements result into False. Else case is optional.

1) Select Case Statement to Check a simple True or False Condition:
In the below example, we have supplied a condition (i.e. J=K) to the Select Case statement. If this is True then ‘Case True’ block will be executed and if it is False then ‘Case False’ block will execute.

Sub Case_Example1()  
'Enter the value for Input variables  
J= InputBox("Enter the value for J:")  
K = InputBox("Enter the value for K:")  
' Evaluating the expression  
Select Case J = K  
Case True  
  MsgBox "The expression is TRUE"  
Case False  
  MsgBox "The expressions is FALSE"  
End Select  
End Sub  

2) Case statement to check Text Strings data:
In this example we will compare text strings in the Case statements. If a match is found then the corresponding Case block will execute otherwise the ‘Case Else’ block will execute.

Sub Case_Example2()  
'Enter the value for variable  Color_name
Color_name = InputBox("Enter the your favorite Color Name:")  
' Evaluating the expression  
Select Case Color_name
Case "Green"  
  MsgBox "You entered Color name as Green"  
Case "Blue"  
  MsgBox "You entered Color name as Blue"  
Case "Yellow"  
  MsgBox "You entered Color name as Yellow"    
Case Else  
  MsgBox "You entered Color name is not the in the Case"    
End Select  
End Sub  

3) Case statement to Check Numbers:
In the below example we will check if the number entered by user is less than or greater than 10.

Sub Case_Example3()  
'Enter the value for  Input variable Num
Num = InputBox("Enter any Number between 1 to 20:")  
' Evaluating the expression  
Select Case Num  
Case Is < 10  
  MsgBox "The Number you entered is less than 10"  
Case Is = 10  
  MsgBox "The Number you entered is Equal to 10"  
Case Is > 10  
  MsgBox "The Number you entered is greater than 10"  
End Select  
End Sub  

Note: You can use IS keyword with Case Statement to compare values.

4) Select Case statement to check multiple conditions inside a single case:
In this example we will enter any number from 1-10. and then we will check if the number is even or odd by using multiple conditions in the Case statement. Notice here I have used a “,” (comma) to check and compare multiple conditions in a single Case statement.

Sub Select_Case_Example4()  
'Enter the value for Input variable Num 
Num = InputBox("Enter any Number between 1 to 10:")  
'Evaluating the expression  
Select Case Num  
Case 2, 4, 6, 8, 10  
  MsgBox "Your Number is Even."  
Case 1, 3, 5, 7, 9  
  MsgBox "Your Number is Odd."  
Case Else  
  MsgBox "Your Number is out of the range."  
End Select  
End Sub  

5) Case statement to check a continuous range as condition:
Here we will test a continuous range as a condition. We will enter any number between 1-10, if the number is between 1 to 5 (including both 1 and 5) then ‘Case 1 To 5’ will be ‘True’, if the number entered by the user is between 6 and 10 (including both 6 and 10) then ‘Case 6 To 10’ will be ‘True’, if both the previous cases are ‘False’ then ‘Case Else’ will be executed.

Sub Case_Example5()  
'Enter the value for Input variable Num 
Num = InputBox("Enter any Number between 1 to 10:")  
'Evaluating the expression  
Select Case Num  
Case 1 To 5  
  MsgBox "Your Number between 1 to 5"  
Case 6 To 10  
  MsgBox "Your Number between 6 to 10"  
Case Else  
  MsgBox "Your Number is out of the range."  
End Select 
End Sub  

6) Case Statement to to Check If a Variable or Range value exist in Multiple data ranges :
We can check if a Range A1 exist between 100 and 500 also between 501 and 1000 also between1001 and 1500 then execute the true Case statement as follows:

Sub Select_Case_Example6()
 X=Range("A1").Value
    Select Case X
        Case 100 To 500, 501 To 1000, 1001 To 1500
             Range("B1").Value =X
       Case Else
             Range("B1").Value = 0
     End Select
End Sub

7) Case Statement to to Check If a Variable or Range value exist in Multiple data ranges and mix of additional Numbers and Text values:
We can check if a Range A1 vale is [100 to 500] , 555 and [600 to 900],999 and [1001 to1500],2000 and "Product","Service" then execute the true Case statement as follows:

Sub Select_Case_Example7()
 X=Range("A1").Value
    Select Case X
           Case 100 To 500, 555,600 To 900, 999,1001 To 1500,2000,"Product", "Service"
                 Range("B1").Value = Range("A1").Value
           Case Else
                 Range("B1").Value = 0
     End Select
End Sub

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------