Excel VBA Select Case Like: 7 Examples

Get FREE Advanced Excel Exercises with Solutions!

Like the other logic-based statement available in different programming languages, the Select Case statement regulates the flow of your code based on various situations. Incorporating the Like operator in your Select Case statement in VBA could be time-saving and handy as it will look for a similar pattern to execute a statement, therefore reducing the task of writing the conditional statement explicitly. In this article, we’ll give a thorough explanation of how to use Excel VBA Select Case Like . Along with examples and best practices, this article will help you develop your skills and produce more efficient and effective code.


Syntax of 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 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.
  • As far as the If Then Else statement concern, 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 a variety of possible outcomes.

How to Launch VBA Editor in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

  • Press Alt + F11 to open your Microsoft Visual Basic.

Opening of VBA editor in Excel worksheet to use Excel VBA Select Case Like statement

  • Then press Insert > Module to open a blank module.

Vba Editor interface


How to Use Select Case & Like Operator Simultaneously in Excel VBA

With the Like Operator, you can perform approximate comparisons. For instance, if there is a match then the code will return TRUE and return FALSE otherwise. Although the Like operator is suitable for use in If Statements, its compatibility with Case Statements is limited. Let’s have an example to understand the scenario fully.


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. Now 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. Thus it is supposed to return True but see the output we have given below.

Return False Statement in MsgBox

Have you found any discrepancies? The code makes the output False. Now the question is why it returns False in spite of having a match within the string. Taking this question further, we will provide the solution in our second case.


Case 2: The Correct Way to Use Select Case Statement

Now here comes the solution to the given task. All you need to do is, 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

As mentioned previously, the Select Case statement in Visual Basic for Applications (VBA) is a useful construct that enables programmers to evaluate a single expression and then run other code blocks based on the result of that expression. By using the Like Operator, the user can determine whether a given string matches a specific pattern or format. In this article, we’ll look at several instances of how to handle string comparisons more effectively in VBA by combining the Select Case statement with the Like Operator.


1. Checking Text String with Select Case Statement and 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.

  • Write the following code in your 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

  • Initially, Productname is declared to the range C5:C16, which contains the names of the products.
  • Then, 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).
  • MsgBox function displays a message box that shows the total sales for each product category.
  • Press the F5 key to see the output as given below.

Alternative Representation: With Colon

The alternative approach of the above mentioned task can be constructed by providing a colon sign between the Case Like statement and executable line. This approach minimizes the code line in your editor.

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

2. Select Case Like Operator with a Range of Letter

Select Case Like Operator with a Range of Letters

Other than searching for specific letters specified in the case statement, we can assign a range of letters to minimize the task. Like the code given below, 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.

  • Run your code and see the output as shown below.

3. Select Case with Multiple Conditions Using And/Or Operator

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. In this way, the Select Case statement becomes more flexible and capable of handling a wider range of possible scenarios. For instance, here we do the Revenue calculation with And operators.

  • Write 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.
  • Next, 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 then 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).
  • Finally, 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.

Read More: How to Use Excel VBA Select Case and Operator


4. Nested Select Case with 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.

  • Run the code and see the output as given below.

 

Read More: How to Use Nested Select Case with VBA in Excel


5. Select Case with Like Operator for Number

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. Give a thought to the code we provide below.

  • Write the following code in your VBA Editor.

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.
  • Now, Run the code and see the output as given below.

Read More: Excel VBA Select Case Between Two Values


6. Select Case for 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.

  • Firstly, we will design a command button to Run our code. Through the task is not mandatory to apply Select Case on the ActiveCell.
  • Go to Developer >> Insert >> Button icon. Drag the Button icon in your worksheet.

Draw the Button from the developer tab

  • Click on the RightButton of your mouse and select Assign Macro.

Select Assign macro to insert vba code

  • Write the following code in your 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 and see the output as shown below.

7. Select Case with Like Operator for Date

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.

  • Write the following code in your VBA Editor.

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.
  • After declaring the variables and assigning the value, next 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.
  • Then, 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.
  • Lastly, 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.
  • Press the F5 key and see the output as given below.

Case and If Statement with Like Operator in Excel VBA

We can also use the Like operator in If Statement to accomplish any logical task. We have shown a simple code here for picturing its usefulness. Firstly, we will look at the utility of the Like operator in Case Statement. Later we will use the If statement in the same code.

  • Write and run the following code in your VBA Editor.

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“.
  • Now see the same as for 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 String Using Excel VBA Select Case

Now if we wish to select a range of cells and figure out whether the cells contain a specific String value or not. This Code might help you.

  • Write and Run the following code in your VBA Editor.

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

  • Define the Select Case statement of 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.

  • Differentiate between the Select Case and If Then Else statements of Excel VBA
  1. You can use the Select Case when you need to compare a single expression with multiple possible outcomes. On the other hand, use the If Then Else statement when you need to evaluate multiple expressions with true/false outcomes.
  2. 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 Practice Workbook

You can download and practice the dataset that we have used to prepare this article.


Conclusion

In conclusion, the Select Case statement is a powerful tool in VBA Excel that allows you to efficiently handle multiple possible outcomes. When combined with the Like operator, the Select Case statement becomes even more flexible, enabling you to perform pattern-matching on strings and make decisions based on the results. Further, if you have any queries related to the given discussion, feel free to ask below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo