Excel VBA Select Case Like: 7 Examples

Syntax of the Select Case Statement

The basic syntax for the Select Case statement in VBA is as follows:

Select Case expression
    Case value1
        'code to be executed if expression matches value1
    Case value2
        'code to be executed if expression matches value2
    Case value3
        'code to be executed if expression matches value3
    ...
    Case Else
        'code to be executed if expression does not match any of the values
End Select

Case Statement Guide:

Case Options	Description	Examples
Is	add operators =, >,<,<>,>=,<=	Case Is = 6
To	Apply for range of numbers	Case 6 To 12
Comma	Apply multiple conditions for one case by adding comma	Case 6, 12, 18 Case 2, Is <40, 10 To 55
No operator	Similar to “Is =”	Case 2 Case “ExcelDemy”

Key Points of Using the Select Case Statement

SELECT CASE allows you to evaluate multiple conditions, which is particularly useful when working with more than two conditions.
The conditional code written with SELECT CASE is more organized compared to traditional conditional code, making it simpler to modify if any of the values need to be adjusted.
The Select Case statement is designed to evaluate a single expression for several potential outcomes, whereas the If Then Else statement is designed to evaluate multiple expressions for two possible outcomes.

How to Launch the VBA Editor in Excel

Press Alt + F11 to open the Microsoft Visual Basic window.
Press Insert and click on Module to open a blank module.

You’ll get a new module where you can insert of paste code snippets.

Vba Editor interface

How to Use Select Case and the Like Operator Simultaneously in Excel VBA

Case 1 – When the Select Case Returns False Statement

As the previous statement states, the Like operator will look for a pattern in your given condition and return True if any of the matches are found. Look at a typical code given below.

Code image When the Select Case Return False Statement

Sub Select_Case_Like_DoesnotWork()
Dim word As String
word = "ExcelDemy"
Select Case word
Case word Like "*ce*m*"
MsgBox "Those letters are contained in the given string"
Case Else
MsgBox "Those letters are not contained in the given string"
End Select
End Sub

From the given code, a string value “ExcelDemy” is stored in the word variable. As we incorporate Select Case Like, it will look for a pattern similar to “*ce*m*” which is present in the given string value. It is supposed to return True but see the output we have given below.

Return False Statement in MsgBox

The code makes the output False.

Case 2 – The Correct Way to Use the Select Case Statement

Make your statement True before entering into the condition.

Code image When the Select Case Return True Statement

Sub Select_Case_Like_DoesWork()
Dim word As String
word = "ExcelDemy"
Select Case True
Case word Like "*ce*m*"
MsgBox "Those letters are contained in the given string"
Case Else
MsgBox "Those letters are not contained in the given string"
End Select
End Sub

Run your code by pressing F5 key and see the output as given below.

Return True Statement in MsgBox

Read More: How to Use VBA Case Statement

Excel VBA Select Case Like: 7 Examples

Example 1 – Checking a Text String with the Select Case Statement and the Like Operator

Revenue Calculation using Select case with like operator

The Select Case with Like operator is used to calculate total sales for different products based on their names. The products are listed in the range C5:C16. The code loops through each cell in the range and applies a Select Case statement to check if the product name matches any of the specified patterns. Depending on the match, the corresponding sales value is added to a variable that keeps track of the total sales for that particular product.

Use the following code in the VBA Editor.

Code for Calculating Revenue

Sub Select_Case_String()
Set Productname = Range("C5:C16")
For i = 1 To Productname.Rows.Count
product = Productname.Cells(i, 1).Value
Select Case True
Case product Like "*Washer"
sum1 = sum1 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
Case product Like "Heat*"
Sum2 = Sum2 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
Case product Like "*Trimmer"
sum3 = sum3 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
Case product Like "F?n"
sum4 = sum4 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
End Select
Next i
MsgBox "Total Sales For Dish Washer: $" & sum1 & ", Heater: $" & Sum2 & ", Trimmer: $" & sum3 & ", Fan: $" & sum4
End Sub

Code BreakDown

Productname is declared to the range C5:C16, which contains the names of the products.
A For loop is assigned to loop through each cell in the Productname range and set the variable product to the value of the current cell.
A Select Case statement is used to check if the product matches any of the specified patterns.
If a match is found, the sales value for that product (located in the second and third columns of the current row) is multiplied and added to the corresponding total sales variable (sum1, Sum2, sum3, or sum4).
The MsgBox function displays a message box that shows the total sales for each product category.

Press the F5 key to see the output.

Alternative Representation With Colon

Providing a colon sign between the Case Like statement and the executable line streamlines the code.

Alternate Code for Calculating Revenue using Colon sign in Select case with like operator

Sub Select_Case_String_With_colon()
Set Productname = Range("C5:C16")
For i = 1 To Productname.Rows.Count
product = Productname.Cells(i, 1).Value
Select Case True
Case product Like "*Washer": sum1 = sum1 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
Case product Like "Heat*": Sum2 = Sum2 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
Case product Like "*Trimmer": sum3 = sum3 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
Case product Like "F?n": sum4 = sum4 + Productname.Cells(i, 2).Value * Productname.Cells(i, 3).Value
End Select
Next i
MsgBox "Total Sales For Dish Washer: $" & sum1 & ", Heater: $" & Sum2 & ", Trimmer: $" & sum3 & ", Fan: $" & sum4
End Sub

Example 2 – Select Case Like Operator with a Range of Letters

Select Case Like Operator with a Range of Letters

We can assign a range of letters to minimize the task. We have used a set of alphabetic letters A-E encapsulated with a third bracket.

Code for Case Like Operator with a Range of Letters

Sub Select_Case_with_Range()
Set Productname = Range("B5:B16")
For i = 1 To Productname.Rows.Count
product = Productname.Cells(i, 1).Value
Select Case True
Case product Like "[A-E]*"
Productname.Cells(i, 2).Value = "Product Name Starts with the Latter between A and E"
Case product Like "[F-R]*"
Productname.Cells(i, 2).Value = "Product Name Starts with the Latter between F and R"
Case Else
Productname.Cells(i, 2).Value = "The string does not match any of the patterns."
End Select
Next i
End Sub

This code categorizes each cell value in the range B5:B16 based on the starting letter of the product name and sets a remark to the next column by using a descriptive string.

Example 3 – Select Case with Multiple Conditions Using AND or OR Operators

Select case with Multiple Conditions Using And/Or Operator

When dealing with more complex situations, it may be necessary to evaluate multiple conditions in order to determine which block of code to execute. In such cases, the And and Or operators can be used to connect two or more conditions within a single Case statement.

We’ll perform Revenue calculation with the And operator.

Use the following code in your VBA Editor.

Select case with Multiple Conditions Using And/Or Operator

Sub MultipleConditions()
Set ProductPrice = Range("D5:D16")
For i = 1 To ProductPrice.Rows.Count
    product = ProductPrice.Cells(i, 0).Value
    quantity = ProductPrice.Cells(i, 2).Value
    Select Case True
        Case product Like "D*" And quantity Like "1##"
        ProductPrice.Cells(i, 3).Value = ProductPrice.Cells(i, 1).Value * ProductPrice.Cells(i, 2).Value
    Case Else
    End Select
Next i
End Sub

Code BreakDown

The code sets a range named “ProductPrice” to the cells in column D, from row 5 to row 16.
The code enters a For loop that runs from 1 to the number of rows in the ProductPrice range. Within the loop, the code sets the variables “product” and “quantity” to the values in the current row of the ProductPrice range.
The code uses a Select Case statement with the True argument to evaluate two conditions:
- The value in the “product” variable starts with the letter “D“.
- The value in the “quantity” variable starts with the number “1” and has two more digits following it.
If both conditions are true, the code multiplies the value in the first column of the current row ( Price) by the value in the third column of the current row (Quantity) and stores the result in the fourth column of the current row (Revenue).
The loop moves on to the next row of the ProductPrice range and repeats until all rows have been evaluated.

Run the code to see the output.

Example 4 – Nested Select Case with the Like Operator

Nested Select Case With Like Operator

Nested Select Case statements with the Like operator in VBA allows you to perform complex conditional branching in your code based on multiple criteria. By nesting Select Case statements, you can create a hierarchical structure of conditions that you can evaluate in a logical order, providing greater flexibility and control over the flow of your code.

Code for Nested Select Case With Like Operator

Sub NestedSelectCase()
Set ProductPrice = Range("D5:D16")
For i = 1 To ProductPrice.Rows.Count
Price = ProductPrice.Cells(i, 1).Value
quantity = ProductPrice.Cells(i, 2).Value
Select Case True
Case Price Like "1#"
Select Case True
Case quantity Like "1##"
ProductPrice.Cells(i, 3).Value = ProductPrice.Cells(i, 1).Value * ProductPrice.Cells(i, 2).Value
End Select
End Select
Next i
End Sub

This code loops through each row in the range “D5:D16“. For each row, it retrieves the price and quantity values of a product. It then uses nested Select Case statements to check if the price is in the range of 10-19 (i.e. it starts with “1“), and if the quantity is in the range of 100-199 (i.e. it starts with “1” followed by two digits). If both conditions are met, it calculates the total cost by multiplying the price and quantity and writes the result to the third column of that row. If the conditions are not met, nothing happens for that row.

Method 5 – Select Case with the Like Operator for Numbers

Using Select Case with Like Operator for Number

As the Like operator enables developers to match the input data against a specified pattern, allowing them to handle multiple values in a single statement. In this scenario, the Select Case statement can evaluate the numeric value against the pattern specified with the Like operator, allowing for efficient and concise code. If we want to calculate the Revenue of the products whose selling quantity is 50-99, we can use the Like operator to accomplish the task.

Code for Using Select Case with Like Operator for Number

Sub SelectCaseWithNumbers()
Set ProductQuantity = Range("E5:E16")
For i = 1 To ProductQuantity.Rows.Count
    quantity = ProductQuantity.Cells(i, 1).Value
    Select Case True
        Case quantity Like "[5-9]#"
        ProductQuantity.Cells(i, 2).Value = ProductQuantity.Cells(i, 1).Value * ProductQuantity.Cells(i, 0).Value
    End Select
Next i
End Sub

In this code, the Select Case statement checks if the quantity is in the range of 50-99 (i.e. it starts with a digit from 5 to 9 followed by any digit). If this condition is met, it calculates the Revenue by multiplying the quantity and Price and writes the result to the Revenue column of that row.

Example 6 – Select Case for the ActiveCell in Excel VBA

One common scenario in Excel VBA programming is the need to evaluate the value of the currently selected cell, or ActiveCell, and take different actions based on its value. The Select Case statement is a useful tool in this context, allowing you to evaluate the ActiveCell and return whether the cell value is string or not.

Go to Developer, then to Insert, and select the Button icon.
Drag the Button icon in your worksheet.

Draw the Button from the developer tab

Right–click on the button and select Assign Macro.

Select Assign macro to insert vba code

Use the following code in the VBA Editor.

Code for Select Case for ActiveCell in Excel VBA

Sub Button1_Click()
Select Case True
Case ActiveCell.Value Like "[A-Z]*"
MsgBox "This cell has a String"
Case Else
MsgBox "This cell has a data other than String"
End Select
End Sub

This code checks if the value in the active cell is a string starting with an uppercase letter. If it is, it displays a message box saying “This cell has a String”. If it’s not, it displays a message box saying “This cell has data other than String”.

Press the F5 key to get the output.

Method 7 – Select Case with the Like Operator for Dates

Select Case with Like Operator for Date

The Like operator can be used with Select Case to perform pattern matching on date values. In this specific example, we will match the date from the Date column and calculate the Revenue for the existing four months, January, February, March, and April, from the dataset.

Code for Select Case with Like Operator for Date

Sub Select_Case_Date()
Dim formattedDate As String
Dim dateValue As Date
Dim arr1(1 To 4)
Set rng1 = Range("B5:B16")
For i = 1 To rng1.Rows.Count
datealue = rng1.Cells(i, 1).Value
formattedDate = Format(datealue, "mm/dd/yyyy") ' convert the date to a string
Select Case True
Case formattedDate Like "01/##/####"
sumjan = sumjan + rng1.Cells(i, 3).Value * rng1.Cells(i, 4).Value
Case formattedDate Like "02/??/????"
sumfeb = sumfeb + rng1.Cells(i, 3).Value * rng1.Cells(i, 4).Value
Case formattedDate Like "03/##/####"
summar = summar + rng1.Cells(i, 3).Value * rng1.Cells(i, 4).Value
Case formattedDate Like "04/##/####"
sumapr = sumapr + rng1.Cells(i, 3).Value * rng1.Cells(i, 4).Value
Case Else
MsgBox "Date does not match any pattern."
End Select
Next i
'Assign the return value to an array
arr1(1) = sumjan
arr1(2) = sumfeb
arr1(3) = summar
arr1(4) = sumapr
'Write the output value to the worksheet
For i = 1 To 4
Cells(20 + i, 4).Value = arr1(i)
Next i
End Sub

Code BreakDown

The first line declares a subroutine called Select_Case_Date, which means that you can execute this code as a macro in Excel.
The next three lines declare two variables, formattedDate as a string and dateValue as a date. The fourth line declares an array called arr1 with 4 elements. We will used this array to store the total sales amounts for each month. The fifth line sets a range object called rng1 to the range B5:B16. This range contains the dates we want to process.

A For loop will iterate each cell in the range rng1 and we assigned the value of each cell to the dateValue variable using the Cells property of the range object. Then, we formatted the dateValue as a string using the Format function with the “mm/dd/yyyy” format string, and assigned the resulting string to the formattedDate variable.
We used the Select Case statement to evaluate the pattern of formattedDate and make a decision based on it. The first Case statement checks whether the formattedDate string matches the patterns “01/##/####”. We used the pound sign (#) as a placeholder for any digit, so the pattern matches any date with January. If the pattern matches, it will calculate the sales amount for that month by multiplying the values in columns D (Price) and E( Quantity) of the current row and adding it to a running total for January. The code does the same job for other Select Case statements, checking whether the formattedDate value matches any other month such as February, March, and April by using a question mark (?) and pound sign (#) as well. Then multiplies the price and quantity value of the corresponding cell to calculate the revenue.
The final Case statement uses the Else keyword to handle any dates that don’t match the previous patterns. If the date doesn’t match any of the patterns, it will display a message box indicating that the date doesn’t match any pattern.
We called the Array arr1() to assign each sales value of given months and return those calculated sums to the worksheet under the Revenue column.

Case and If Statement with the Like Operator in Excel VBA

We can also use the Like operator in If Statement to accomplish any logical task. We will look at the utility of the Like operator in Case Statement then use the If statement in the same code.

Code for Case Statement with like operator

Sub If_Vs_SelectCase_Statement()
age = InputBox("Write your age here")
Select Case True
Case age Like "[2-9]#"
MsgBox ("You are not a Child anymore")
Case age Like "1[8-9]"
MsgBox ("You are not a Child anymore")
Case Else
MsgBox ("You are still a Child")
End Select
End Sub

This code prompts you to input your age via an input box. Then it uses a Select Case statement to evaluate the age value. If the age is between 20 and 99 (i.e. the age matches the pattern “[2-9]#“), or if the age is between 18 and 19 (i.e. the age matches the pattern “1[8-9]“), it displays a message box saying “You are not a Child anymore“. If the age doesn’t match either of these patterns, it displays a message box saying, “You are still a Child“.

Here’s the same process using the If Statement given below.

Code for If Statement with like operator

Sub If_Vs_SelectCase_Statement2()
age = InputBox("Write your age here")
If age Like "[2-9]#" Then
MsgBox ("You are not a Child anymore")
ElseIf age Like "1[8-9]" Then
MsgBox ("You are not a Child anymore")
Else
MsgBox ("You are still a Child")
End If
End Sub

Checking Whether a Cell Contains a String Using Excel VBA Select Case

We wish to select a range of cells and figure out whether the cells contain a specific String value.

Here’s the code you’ll need.

Code for Select Case with String Contains

Sub String_Contains()
Dim cell As Range
For Each cell In Selection
If InStr(1, cell, "Fan", 1) Then MsgBox ("String Fan is there")
Next
End Sub

This code checks if the selected cells contain the substring “Fan” and displays a message box if it finds it.

Frequently Asked Questions

Definition of the Select Case statement in Excel VBA

The Select Case statement is a comparison tool that allows you to check an expression against multiple conditions. It evaluates each case statement in order, stopping when it finds a match. If none of the conditions match, it will execute the code under the optional Case Else statement.

Differences between the Select Case and If Then Else statements of Excel VBA

You can use the Select Case when you need to evaluate a single expression for multiple possible outcomes. On the other hand, use the If Then Else statement when you need to evaluate multiple expressions with true/false outcomes.
The Select Case statement is more readable and efficient for multiple conditions whereas the If Then Else statement is more suitable for nested conditions and complex logic.

Download the Practice Workbook

Select Case with Like.xlsm