How to use VBA select case statement

In Microsoft Excel everybody uses If-Then-Else statement every now and then. If statement is useful if you have a limited number of conditions. But let's assume you have to match around 100 conditions, in that case If statement will take a lot of code to be copied and pasted and also to execute.
Instead of using If statement in this scenario you can use CASE statement.

Syntax:

Select Case test_expression
Case condition_1
result_1

Case condition_2
result_2
.
.
.

Case condition_n
result_n

Case Else
result_else

End Select

test_expression is a string or numeric value. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n are evaluated in the order listed. Once a condition is found to be true, the CASE statement will execute the corresponding code and not evaluate the conditions any further.
result_1 to result_n is the code that is executed once a condition is found to be true.

Note: If no condition is met, then the Else portion of the CASE statement will be executed. It is important to note that the Else portion is optional.

Example:
Let's take a look at a simple example:

Select Case InputRegion
Case "N"
Region = "North"
Case "S"
Region = "South"
Case "E"
Region = "East"
Case "W"
Region = "West"
End Select

You can also use the To keyword to specify a range of values. For example:

Select Case Number
Case 1 To 10
Region = "North"
Case 11 To 20
Region = "South"
Case 21 To 30
Region = "East"
Case Else
Region = "West"
End Select

You can also use comma delimit values. For example:

Select Case Number
Case 1, 2
Region = "North"
Case 3, 4, 5
Region = "South"
Case 6
Region = "East"
Case 7, 11
Region = "West"
End Select

And finally, you can also use the Is keyword to compare values. For example:

Select Case Number
Case Is < 100
Region = "North"
Case Is < 200
Region = "South"
Case Is < 300
Region = "East"
Case Else
Region = "West"
End Select

I think this has been informative and I thank you for viewing.

-Saurabh

AttachmentSize
SelectCase.xlsm16.78 KB