Table of Contents
The “If Cell Contains” formula in VBA allows you to perform a certain action when a specific text or value is present in a cell. You can use this formula in VBA by first creating a named range for the range of cells you want to check, then using a loop to iterate through each cell in the range and check if the cell contains the specific value or text. If it does, you can then perform the desired action. If not, you can move on to the next cell. This process can be repeated for any range of cells you want to check.
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 .