In this article we will see how to automate the beginning of a certain action when a cell in a spreadsheet contains a certain value. There are three ways to do it: InStr, please and find.
A tool is a function that displays the position of the searched text in another text. In this example we use the Instr() function to check if a cell contains the word apple. We use the Do-loop to scroll through all recordings in column A and check each recording.
alt= sample data for device data Width-original width=650 Height-original height=222 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/Excel-VBA-If-Cell-Contains-Value-Then.png data-ez= />
alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />
Sub Use_Instr()
R = 1 1 1
‘Run until the last string
Do Until Range(A & R) =
‘Check each cell to see if it contains an ‘apple’, then…
…location Contains an apple in column B
If InStr(1, Range(A&R), Apple),
Range(B&R) = Contains an apple
End If
R = R + 1 1
Loop
End Sub
After executing the above code, we get the following results:
alt=A first time data with instr – some data was found but not all data – original width=682 data – original height=192 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604106089_270_Excel-VBA-If-Cell-Contains-Value-Then.png data-ez= />.
alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />
The reason we couldn’t catch the apples in row 5 is because they have a capital A. To make the InStr case insensitive, we can use vbTextCompare as follows
Sub Use_Instr_ignoreLetterCase()
R = 1 1 1 1
‘Walk to last string
Do Until Range(A & R) =
‘Check each cell to see if it contains an ‘apple’, then….
…place Contains an apple in column B
, if InStr(1, Range(A&R), apple, vbTextCompare) then
Range(B&R) = Contains an apple
End I
R = R + 1
Loop
End Sub
We have now correctly identified the apple in column 5.
alt=end of device passage for both cases data-orig-width=760 data-orig-height=201 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2020/10/1604106090_370_Excel-VBA-If-Cell-Contains-Value-Then.png data-ez= />
alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />
alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />
We can use Like instead of InStr as shown below. Note that we use the Text Comparison option at the top of the module to ignore the sensitivity of the cases when searching for words/letters. Also note the asterisk *, which we place before and after the search term. The following code gives the same results as the previous sample.
Option Compare text
Sub Use_Instr()
R = 1 1 1 1
‘Walk to last row
Doe Tot Range(A&R) =
‘Check each cell to see if it contains an ‘apple’, then…
…place Contains apple in column B
If Range (A&R) If *Appel* Then
Range (B&R) = Contains apple
End If
R = R + 1
Loop
End Sub
Using the search method can be very useful if you don’t have to worry about building cyclic logic on specific cells. The direction of the integrated cyclic movement of the search function can even be reversed, and not only forwards. I will give a very simple example of using Find here. More information can be found in this article: https://software-solutions-online.com/excel-vba-find-find-value-range-cells-vba/.
Sub Use_Find()
‘specifies the last row of 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)
‘stores the address of the first search result, because the search does not start again until all matches are found.
firstCellAddress = SearchResult.address
‘If the search result is nothing = Error, do the following
If the search result is nothing = Error, do the following
Enter the number
Location Contains an apple to the right of the cell with the search result.
searchResult.offset(0, 1) = Contains Apple
‘Keep search for the next match (default direction is down)
Set searchResult = searchRange.FindNext(searchResult)
‘Keep looping until the search result is the first one we found
‘ (until we loop through the results we already found)
Loop during firstCellAddress <> searchResult.Address
End If
Last sub-heading
In this article we have shown three methods to check whether a cell contains a certain value. Finally, it is important not to forget the sensitivity of the register, as it is one of the most common sources of errors.
Related Tags:
vba if cell contains any text,vba if cell contains number,if cell.value = then vba,istext in vba,if cell contains text then msgbox,excel vba if range contains specific text,run macro if cell value equals,excel vba if formula in cell,excel vba if cell is blank then,excel vba if cells like,vba check if cell is equal to string,vba nested if then else in a for next loop,check if a cell contains a substring vba,if active cell contains text then vba,if cell in range contains text then vba,excel macro contains,excel if contains partial text,if cell is not blank,cell a20 display an orange background,excel check if cell is empty,vlookup contains,you want to add a column to the pivot table,excel vba if then multiple statements,vba if 2 criteria,double conditional vba,vba logical operators,excel vba check if two conditions are true,if cell contains specific text then return value in another cell vba,if range( ).value = then,excel vba if statement based on cell value,excel vba if cell contains any text,excel vba if cell contains partial text,excel vba if cell contains specific text then delete row,excel vba if cell contains number,vba if column contains value then
