Excel VBA : Select Case Statement (Alternative to IF THEN ELSE)

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 :
Select Case [Expression]
          Case Condition1
           'Do something
           Case Condition2
            'Do something
          Case Else
            'Do something
End Select 
Download the workbook used in the examples shown below.

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 Single
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
 The output is shown in the image below :


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()
    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
Download the workbook 

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Excel VBA : Select Case Statement (Alternative to IF THEN ELSE)"

Post a Comment

Next → ← Prev