In this article, we will look at how to automate the launching of a particular action when a cell on the worksheet contains a particular value. There’re three ways that we can do this with; InStr, Like, and Find.

Example 1: INSTR

Instr is a function that returns the position of text that you are looking within other text. In this example, we will use Instr() to check if a cell contains the word ‘apple’. We will use a Do Loop to cycle through all the sentences in column A and do the check on each of them.

Sub Use_Instr()
R = 1
'loop to the last row
Do Until Range("A" & R) = ""
'check each cell if if contains 'apple' then..
'..place 'Contains Apple' on column B
If InStr(1, Range("A" & R), "apple") Then
Range("B" & R) = "Contains Apple"
End If
R = R + 1
Loop
End Sub

After we run the above code, we get the following results:

The reason we couldn’t catch the apples on row 5 is because it has a capital ‘A’. To make InStr become insensitive to the letter’s case, we can use vbTextCompare as shown below:

Sub Use_Instr_ignoreLetterCase()
R = 1
'loop to the last row
Do Until Range("A" & R) = ""
'check each cell if if contains 'apple' then..
'..place 'Contains Apple' on column B
If InStr(1, Range("A" & R), "apple", vbTextCompare) Then
Range("B" & R) = "Contains Apple"
End I
R = R + 1
Loop
End Sub

Now we have identified ‘apple’ in the 5^th column correctly.

Example 2: Like

We can use Like instead of InStr as shown below. Note that we use Option Compare Text by placing it at the top of the module in order to make our search for words/letters become case insensitive. Also note the asterisk * that we place before and after the search word. The following code gives us the same exact results as the previous example.

Option Compare Text
Sub Use_Instr()
R = 1
'loop to the last row
Do Until Range("A" & R) = ""
'check each cell if contains 'apple' then..
'..place 'Contains Apple' on column B
If Range("A" & R) Like "*apple*" Then
Range("B" & R) = "Contains Apple"
End If
R = R + 1
Loop
End Sub

Example 3: Find

Using the Find method can be extremely helpful if we don’t want to worry about constructing the logic for cycling through particular cells. The direction of the built-in cycling of the Find function could even be backwards, not just forwards. Here, I will present a very simple example for using Find. To understand Find in detail, check out this article: https://software-solutions-online.com/excel-vba-find-find-value-range-cells-vba/

Sub Use_Find()
'determine last row on column A
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set searchRange = Range("A1:A" & lastRow)
Set searchResult = searchRange.Find("apple", MatchCase:=False, Lookat:=xlPart)
'store the address of the first search result because Find will just start from it again once it finds all the matches.
firstCellAddress = searchResult.Address
'if there’s something in the search Result then do the following
If searchResult Is Nothing = False Then

    Do
       'place 'Contains Apple’ to the right of the cell that contain the search result.
        searchResult.Offset(0, 1) = "Contains Apple"
        'continue searching for the next match (the default direction is downwards)
        Set searchResult = searchRange.FindNext(searchResult)
        'keep looping as long as the search result is not the first one that we found
        ' (as long as we’re not     looping through the results we already found again)
    Loop While firstCellAddress <> searchResult.Address

End If

End Sub

Summary

In this article, we have demonstrated the three methods you can use to check if a cell contains a particular value. Finally, it’s important to not forget to account for case sensitivity as this is one of the most common sources of errors.