You can use the following basic syntax to use a formula for “if cell contains” in VBA:
Sub IfContains()
Dim i As Integer
For i = 2 To 8
If InStr(1, LCase(Range("A" & i)), "turtle") <> 0 Then
Result = "Contains Turtle"
Else
Result = "Does Not Contain Turtle"
End If
Range("B" & i) = Result
Next i
End Sub
This particular example checks if each cell in the range A2:A8 contains “turtle” and then assigns either “Contains Turtle” or “Does Not Contain Turtle” to each corresponding cell in the range B2:B8.
Note: The Instr method checks if one string contains another string and the LCase method converts text to lowercase to perform a case-insensitive search.
The following example shows how to use this syntax in practice.
Example: How to Use “If Cell Contains” in VBA
Suppose we have the following list of cells in Excel that each contain various text:
Suppose we would like to check if each cell in the range A2:A8 contains the text “turtle” and output the results in the corresponding cells in the range B2:B8.
We can create the following macro to do so:
Sub IfContains()
Dim i As Integer
For i = 2 To 8
If InStr(1, LCase(Range("A" & i)), "turtle") <> 0 Then
Result = "Contains Turtle"
Else
Result = "Does Not Contain Turtle"
End If
Range("B" & i) = Result
Next i
End Sub
When we run this macro, we receive the following output:
Column B tells us whether or not the corresponding cells in column A contain “turtle” somewhere in the text.
Note: You can find the complete documentation for the VBA Instr method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String