SELECT CASE statement in Excel VBA

SELECT CASE is another statement for decision making while you have 3 or more condition. Definitely you can use IF statement instead of SELECT CASE statement. The convenient thing is that of applying the SELECT CASE is, it makes the macro reading capability faster than IF statement. Rather than using IF statements, SELECT CASE statement is very clear in logical operation. It supports nested condition same as IF statement. In others programming language SELECT CASE statement is also known as SWITCH Statement.


Example 1:

This is simple example of SELECT Case statement. The working process is as below:

Image 1: Simple Select Case Statement

Now write the below codes in your module and run it:

Sub selcase1()
       Dim x As String
       Dim y As String
 
       x = "Microsoft"
       y = "Excel"

       Select Case x = y
              Case True
                     MsgBox "This is True"
              Case False
                     MsgBox "The expressions is FALSE"
       End Select
End Sub

Image 2: Result

Example 2:

This is medium example. Here 2 SELECT Case statements have used. The diagram will show the working process of this type of SELECT Case statement which has given below:

Image 3: Medium Select Case statement process

Assume that, you are trying to find out the incentive Taka for the Sales Team based on a Incentive Slab like below:

Image 4: Sample Incentive calculation table

Here you can use 2 SELECT CASE statement. One is for verifying that, the incentive is eligible and another one is for not eligible. Open the module and write down the below codes:

Sub in_calc()
    Dim y As Double
    Dim x As Integer
    For x = 4 To 9
        y = Cells(x, 4).Value / Cells(x, 3).Value
        Cells(x, 5) = y
        Select Case y
            Case Is >= 0.8
                Select Case y
                    Case Is >= 1.2
                       Cells(x, 6).Value = 12000
                Case Is >= 1
                        Cells(x, 6).Value = 10000
                Case Is >= 0.8
                    Cells(x, 6).Value = 8000
            End Select
            Case Is <= 0.79
                Cells(x, 6).Value = "Not Eligible"
        End Select
    Next x
End Sub

The result will display like below:

Image 5: Incentive Calculated

By this way you can use SELECT CASE statement easily. You can use CASE ELSE like ElseIF.


Example 3:

Another example is, Assume that Column A contains a value in between 1 to 13. You have to identify the Quarter number for Incentive calculation.

Image 6: Sample data

Now use the below code in your module:

Sub qtrc()
   Dim x As Integer
   For x = 2 To 13
      Select Case Cells(x, 1).Value
         Case 1, 2, 3
            Cells(x, 2).Value = "Q1"
         Case 4, 5, 6
            Cells(x, 2).Value = "Q2"
         Case 7, 8, 9
            Cells(x, 2).Value = "Q3"
         Case 10, 11, 12
            Cells(x, 2).Value = "Q4"
         Case Else
            Cells(x, 2).Value = "Wrong"
     End Select
   Next x
End Sub

The result will be looks like as below:

Image 7: Result

|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw