Table of Contents
The Like operator in VBA is a comparison operator that allows you to check if a string of characters matches a specific pattern. It is commonly used in conjunction with the “If” statement to perform conditional actions based on the result of the comparison. The syntax for using the Like operator is “string Like pattern”, where “string” is the string of characters you want to compare and “pattern” is the specific pattern you want to match. Some examples of using the Like operator in VBA include checking if a string contains a specific set of characters, if it starts or ends with a certain letter or if it follows a specific format. This operator is particularly useful in handling large amounts of data and automating repetitive tasks.
Use Like Operator in VBA (With Examples)
You can use the Like operator in VBA to determine if a string contains a specific pattern.
For example, you can use the following syntax to check if each string in the cell range A2:A10 contains the substring “hot” and output the results in the range B2:B10:
Sub CheckLike()
Dim i As Integer
For i = 2 To 10
IfRange("A" & i) Like "*hot*"ThenRange("B" & i) = "Contains hot"ElseRange("B" & i) = "Does not contain hot"End IfNext i
End Sub
The following example shows how to use this syntax in practice.
Example: How to Use Like Operator in VBA
Suppose we have the following list of foods in Excel:
We can create the following macro to check if each string in column A contains the substring “hot” and output the results in column B:
Sub CheckLike()
Dim i As Integer
For i = 2 To 10
If Range("A" & i) Like "*hot*" Then
Range("B" & i) = "Contains hot"
Else
Range("B" & i) = "Does not contain hot"
End IfNext i
End Sub
When we run this macro, we receive the following output:
Column B shows whether or not each corresponding cell in column A contains the substring “hot” or not.
Note that we used asterisks ( * ) around the substring to indicate that any character can come before or after the string “hot” in the cell.
If we would instead like to check if each string starts with “hot” then we could place an asterisk only after the substring:
Sub CheckLike()
Dim i As Integer
For i = 2 To 10
If Range("A" & i) Like "hot*" Then
Range("B" & i) = "Starts with hot"
Else
Range("B" & i) = "Does not start with hot"
End IfNext i
End Sub
When we run this macro, we receive the following output:
Additional Resources
The following tutorials explain how to perform other common tasks using VBA: