Excel VBA Select Case Like: 7 Examples

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 Select Case Like in VBA Excel. Along with examples and best practices, this article will help you develop your skills and produce more efficient and effective code.

Download Practice Workbook

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

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:

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.

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

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.

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.

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.

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.

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

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.

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

• Press the F5 key to see the output as given below.

Alternative Representation: With Colon

The alternative approach of the abovementioned 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.

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

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.

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

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.

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

• Run the code to see the output.

4. 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.

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.

5. 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.

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

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

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.

• Click on the Right–Button of your mouse and select Assign Macro.

• Write the following code in your VBA Editor.

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

• Press the F5 key and see the output as shown below.

7. 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.

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

• 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.

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

• Now see the same as for the If Statement given below.

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.

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

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.

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.