Excel VBA Like Operator with Variable (4 Examples)

In this article, we will explore some ways of using Excel VBA Like operator with variable. The Like operator compares a string against a pattern. When using the Like operator, you can include wildcard characters to match any character or characters in a string. This can be incredibly useful for tasks such as filtering data or searching for specific information in large datasets.

Excel VBA Like Operator with Variable: 4 Suitable Examples

Excel VBA Like Operator is a powerful tool that can be assigned to a variable to manipulate and analyze data in spreadsheets. One of the key advantages of using the Like operator is its ability to handle pattern matching with wildcard characters, allowing you to perform searches and comparisons on data with greater precision and flexibility. Suppose you have a large dataset containing customer information and you need to filter it by specific criteria, such as all customers whose names start with “J“. You can use the Like operator with a variable to extract all names that start with “J” from the dataset. In this article, we will provide you with 4 different approaches to utilizing Like Operator in a VBA code.

1. Excel VBA Like Operator with Question Mark

One of the wildcard characters that can be used with the Like operator is the question mark (?), which represents a single character in the pattern. Suppose you have a list of product codes and want to filter out all codes that end in a specific character followed by the digit 123. You can use the Like operator with the question mark wildcard to achieve this.

Sub With_Question_Mark()
Set Product_code = Range("D5:D14")
pattern = "?123"
For Each cell In Product_code
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

Sub With_Question_Mark()

Set Product_code = Range("D5:D14")

pattern = "?123"

For Each cell In Product_code

Next cell

2. VBA Like Operator with Hash Mark

Like the question mark, the Hash mark (#) is another wild card that will look for a single digit in the pattern. This can be useful in scenarios where you need to filter data based on a specific pattern that includes a variable digit.

For example, you have a list of customer ID and want to filter out all ID starting with 04. You can use the Like operator with the hash mark wildcard to achieve this.

Sub With_Hash_Mark()
Set Customer_id = Range("B5:B14")
pattern = "04-###"
For Each cell In Customer_id
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

Sub With_Hash_Mark()

Set Customer_id = Range("B5:B14")

pattern = "04-###"

For Each cell In Product_code

Next cell

3. Excel VBA Like Operator with Asterisk

Among the wildcard characters available in the Like operator, the asterisk (*) is particularly useful for representing any number of characters in the pattern. The asterisk sign is used to look for one or more than one similar patterns in a given dataset. The location of the asterisk in the pattern variable has a different meaning. 

3.1 Asterisk at the Beginning

The asterisk can be used to filter data based on a specific suffix upon use at the beginning of a pattern. For example, if you have a dataset and want to filter the data which contains the Hoodie word in your Product Name column. You can achieve the task by putting an asterisk sign before the Hoodie word.

Sub Asterisk_at_the_beginning()
Set Product_name = Range("E5:E14")
pattern = "*Hoodie"
For Each cell In Product_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

The abovementioned code will look for a match against the string value “*Hoodie”. The * character in the pattern represents any number of characters, so any value that ends with Hoodie will be included in the filter.

Then the code uses a For loop to iterate through each cell in the Product_name range. For each cell, the code uses the Like operator to check if the cell’s value matches the pattern. If the value does match the pattern, the entire row of the cell is set to visible using the cell.EntireRow.Hidden = False line. If the value does not match the pattern, the entire row of the cell is hidden using the cell.EntireRow.Hidden = True line.

3.2 Asterisk in the End

When you use an asterisk at the end of the pattern in VBA Like operator, it represents any number of characters after a specific substring. For Example, consider a dataset and you want to filter the row which contains the Black prior to any word in your Product Name column. You can put an asterisk sign after the Black word to achieve the task.

Sub Asterisk_in_the_End()
Set Product_name = Range("E5:E14")
pattern = "Black*"
For Each cell In Product_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

This code searches for a specific pattern “Black*” which means it will match any string that starts with Black and can have any number of characters following it. Once it finds the match the corresponding row will be shown, if not then the rows will be hidden.

3.3 Asterisk at Both Ends 

When you add two asterisk signs before and after or a group of characters, then the Like operator will look for a similar pattern where the word contains the same character in itself. For Example, you put two asterisk signs before and after the S letter (*S*), thus the Like operator will find the cells which have the word containing capital S in the middle of any position of the word.

Sub Asterisk_in_Both_End()
Set Product_name = Range("E5:E14")
pattern = "*S*"
For Each cell In Product_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

This code searches for a specific pattern “*S*” which means it will match any cell that contains the letter S in the middle of any position of the string. Once it finds the match the corresponding row will be shown, if not then the rows will be hidden.

4. Excel VBA Like Operator with Square Brackets

The square brackets in the Like operator in VBA are used to specify a range of characters that can occur in the string. For example, if you want to match a string that contains any character from a to z, or the Capital letter of them, you can encapsulate your desired letter or letters in a third bracket. (eg: *[B], *[B-F]*, *[!B-F] )

4.1 Matching of Any Uppercase Character from Alphabet

To match any uppercase character from the alphabet using the Like operator with square brackets in VBA, you can use the following pattern: [your desired letter]*. For example, you want to filter the row which contains Bob in the Customer Name column. To accomplish the task you can follow the code given below and have a better understanding of it.

Sub Matching_Any_Uppercase()
Set Customer_name = Range("C5:C14")
pattern = "[B]*"
For Each cell In Customer_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

4.2 Not Matching of Any Uppercase Character from Alphabet

To match any string that does not contain any uppercase letter from the alphabet, you can use [!Something that you don’t want to see], (04-11[!T] for instance) with the Like operator in VBA.

Sub Excluding_Any_Uppercase()
Set Customer_id = Range("B5:B14")
pattern = "04-11[!T]"
For Each cell In Customer_id
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

The code loops through a range of cells (B5:B14) containing customer IDs and hides or shows rows based on whether the customer ID matches a specified pattern (“04-11[!T]” in this case).

The pattern 04-11[!T] matches any string that starts with 04-11 and is followed by any character except for “T”. 

4.3 Matching of Any Uppercase or Lowercase Character from Alphabet

To match any string that contains at least one character from the alphabet (uppercase or lowercase), you can use *[A-Za-z]* with a Like operator in VBA. In our specific example given below, we use W[H-Zh-z]lly as a pattern. Thus, the pattern matches any string that starts with a W, followed by any character from H to Z or h to z, followed by lly. So, for example, WElly, WJlly, Wzlly, Whlly, and Willy would be shown, but WAlly and WLLY would be hidden.

Sub Including_Any_Character()
Set Customer_name = Range("C5:C14")
pattern = "W[H-Zh-z]lly"
For Each cell In Customer_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

The code works by first defining the range Customer_name and the pattern to match. It then loops through each cell in the range and checks if the cell’s value matches the pattern using the Like operator. If the cell’s value matches the pattern, the entire row is shown (using the .EntireRow.Hidden = False statement), the cell will be hidden otherwise.

4.4 Matching of Single Character from a Char List

Following the previous case, [A-E]* will look for the cell containing a word starting with any character in the A-E range, followed by zero or more characters of any type. So, for example, Anne, Bob, Cathy, David, and Emma would be shown, but Frank and Zoe would be hidden.

Sub Matching_Any_Single_Character_From_Char_list()
Set Customer_name = Range("C5:C14")
pattern = "[A-E]*"
For Each cell In Customer_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

In this code the pattern [A-E]* will look for the word starting with the letter A-E using the Like operator. If the cell’s value matches the pattern, the entire row is shown (using the .EntireRow.Hidden = False statement), and the cell will be hidden otherwise.

4.5 Matching of Single Character Excluding the Char List

Now if the condition demands to find the customer name starting with any letter except A to E, you can follow the code provided below.

Sub Matching_Any_Single_Character_Excluding_the_Char_List()
Set Customer_name = Range("C5:C14")
pattern = "[!A-E]*"
For Each cell In Customer_name
If cell.Value Like pattern Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

In this code the pattern [!A-E]* will look for the word starting any letter other than A-E using the Like operator. If the cell’s value matches the pattern, the entire row is shown (using the .EntireRow.Hidden = False statement), the cell will be hidden otherwise.

Frequently Asked Questions

• How do you reference a cell with a variable in VBA?

In VBA, you can reference a cell with a variable by using the Range object and concatenating the variable with the cell address.

For example, if you have a variable named myCell that contains the address of the cell you want to reference, you can use the following code to reference the cell:

Dim myCell As String
myCell = "A1" ' Set the value of the variable to the cell address
Range(myCell).Value = "Hello" ' Set the value of the cell to "Hello"

• How do you add a like condition in Excel?

You can add a LIKE condition in Excel by using the MATCH function with the * wildcard character.

• How to compare a string to a variable in VBA?

To compare a string to a variable in VBA, you can use an If statement and the StrComp function.

Download Practice Workbook

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

Conclusion

In conclusion, using the Excel VBA Like operator with variable is a powerful tool for performing pattern matching and string manipulation. By combining it with variables, you can make your code more flexible and efficient. However, you might already notice there are multiple ways and combinations can be made, so before creating any pattern make sure it is aligned with your work type. Further, if you have any queries related to this article, feel free to comment below. Cheers!