The SELECT CASE statement is an alternative to IF THEN ELSE statement. It is used to perform some action when a condition is met,
The syntax for SELECT CASE statement is as follows :
Example 1
You have data. You want a function that returns 1 if value is greater than 100. If value is greater than 50 but less than or equal to 100, it should return 0.5. Else it should return 0.
Select Case Statement with "To" Keyword
The "To" keyword is used to specify lower and upper limit of the range for the expression being evaluated.
Example 2
You have data. You want a function that returns 1 if value is greater than 0 but less than or equal to 30. If value is greater than 30 but less than or equal to 50, it should return 0.5. If value is greater than 50 but less than equal to 100, it should return 0.25. Else it should return 0.
Example 3
If value of cell A1 is not only between 50 and 100 but also between 300 and 500, value of cell B1 should be equal to value of cell A1. Otherwise it should be 80 percent of cell A1 value.
The syntax for SELECT CASE statement is as follows :
Select Case [Expression]Download the workbook used in the examples shown below.
Case Condition1
'Do something
Case Condition2
'Do something
Case Else
'Do something
End Select
Example 1
You have data. You want a function that returns 1 if value is greater than 100. If value is greater than 50 but less than or equal to 100, it should return 0.5. Else it should return 0.
Function condition (exp As Single) As Single
Select Case exp
Case Is > 100
condition = 1
Case Is > 50
condition = 0.5
Case Else
condition = 0
End Select
End Function
The output is shown in the image below :
Select Case Statement with "To" Keyword
The "To" keyword is used to specify lower and upper limit of the range for the expression being evaluated.
Example 2
You have data. You want a function that returns 1 if value is greater than 0 but less than or equal to 30. If value is greater than 30 but less than or equal to 50, it should return 0.5. If value is greater than 50 but less than equal to 100, it should return 0.25. Else it should return 0.
Function condition1(exp As Single) As SingleThe output is shown in the image below :
Select Case exp
Case 0 To 30
condition1 = 1
Case 31 To 50
condition1 = 0.5
Case 51 To 100
condition1 = 0.25
Case Else
condition1 = 0
End Select
End Function
Example 3
If value of cell A1 is not only between 50 and 100 but also between 300 and 500, value of cell B1 should be equal to value of cell A1. Otherwise it should be 80 percent of cell A1 value.
Sub condition2()Download the workbook
Select Case Range("A1").Value
Case 50 To 100, 300 To 500
Range("B1").Value = Range("A1").Value
Case Else
Range("B1").Value = Range("A1").Value * 0.8
End Select
End Sub
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Share Share Tweet