Excel VBA: If Cell Contains Value Then
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 5th 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
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.