How to Find an Exact Match Using VBA
Contents
The Search Feature in Microsoft Excel
Before digging into how to look for exact matches in VBA, I’ll give you some context with the easiest way to accomplish this goal — the search function in Excel.
Microsoft Excel provides us with an excellent feature that can help in searching for a word or text, which is very difficult to do in a hardcover book. I used to think that this was a blessing in disguise given to us by the digital era. The dialog box seen in the image below also provides a customized search with options such as:
- Search within the active sheet alone/the entire workbook
- Search by rows/columns
- Look through all formulas/values/notes/comments
It also has checkboxes to choose if we want to do a case-sensitive search/look only for entire cell content that matches the search term. These checkboxes are not selected by default. This means that a case insensitive search trying to match any part of cells is done by default.
Find Function in VBA
The search functionality explained above is also achieved through Visual Basic for Applications using the Find function.
Syntax:
Myrange = <expression>.Find ( <What> , <After what > , < LookIn > , < LookAt > , < Search Order > , < Search Direction >, < Match Case > , < Match Byte > , < Search Format > )
where
Myrange is the return value – A range object . This object represents the first cell that matches the search. Nothing is returned if a match is not found.
< expression >
is a variable that indicates a Range object–the range within which you want to search.
Other parameters of the find function are explained below.
Name | Required/Optional | Data type | Description |
What | Required | Variant | The data to search for. It can be a string or any Microsoft Excel data type. |
After what | Optional | Variant | The reference of the cell after which the search should begin. This corresponds to the position of the cell that is active when a search is done from the user interface. ⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺ Notice that After what must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around this cell.⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺⸺ If value is not provided for this argument, the search starts after the cell in the upper-left corner of the range. |
LookIn | Optional | Variant | Can be one of the following values : XlFindLookIn constants: xlFormulas, xlValues, xlComments, or xlCommentsThreaded. |
LookAt | Optional | Variant | Can be one of the following values : XlLookAt constants: xlWhole or xlPart. |
Search Order | Optional | Variant | Can be one of the following values: XlSearchOrder constants: xlByRows or xlByColumns. |
Search Direction | Optional | Variant | Can be one of the following values: XlSearchDirection constants: xlNext or xlPrevious. |
Match Case | Optional | Variant | The default value is False. Use value “True” to make the search case-sensitive. |
Match Byte | Optional | Variant | This parameter is used only if you have selected or installed double-byte language support. Value should be “True” to have double-byte characters match only double-byte characters. “False” to have double-byte characters match their single-byte equivalents. |
Search Format | Optional | Variant | The search format. |
Example: Finding a Value in a Range of Cells
In this program, we define a range and a value to search for (we are searching for the word “COUNT” here). Then, using the Find method we find the first cell in the defined range that contains the given search word/text. Once it is found the address of the cell is stored in another string variable. We are using a default search here which is not case sensitive/not looking for whole textual content.
Sub find_demo() ' declare a range object Dim r1 As Range ' declare a string variable to store the address of the searched item Dim Address1 As String ' Setting up the range and value to be searched for. Here we are searching for the word "COUNT" Set r1 = Sheets("Finddemo").Range("A1:A50").Find("COUNT", LookIn:=xlValues) ' If the search item is found in a cell / if r1 has a value If Not r1 Is Nothing Then ' assign the item's address to address1 variable Address1 = r1.Address ' print the first location where the search term is found Debug.Print r1 & " in " & Address1 End If End Sub
Contents of the “Finddemo” sheet to run the program above.
Now in this program, if we want to find more matches for the search word, we can use the code below.
'vba statement equal to the findnext button of the find dialog box Set r1 = Sheets("Finddemo").Range("A1:A50").FindNext(r1) address2 = r1.Address 'Print the next location of the search term Debug.Print r1 & " in " & address2
Per the data in the “Finddemo” sheet (available above as an image), the word “COUNT” is available in the cells A13, A14 and A16. So, all that will get printed as output.
Example: Simple VBA Program to Find and Replace
This program can do a search and replace of text in the given range.
In this program, we replace all “Pizza” with “Burger” in the cells range A1 to A25 in the worksheet “Finddemo.”
To try different values and ranges, the variable values in the program’s code can be changed. Please change values and try running the codes with different sample data to understand the working of the function.
Sub FindString_demo() ' declare 2 variables - one range object and one string Dim r1 As Range Dim Addressofcell1 As String ' with statement is used to avoid repetition of the piece of code in every statement. With Worksheets("Finddemo").Range("A1:A25") ' initiate r1 . xlvalues states that we are looking for matching values. The search word here is "Pizza" Set r1 = .Find("Pizza", LookIn:=xlValues) ' check if r1 has a value If Not r1 Is Nothing Then ' if yes , we capture its address Addressofcell1 = r1.Address ' we enter into a loop that replaces all occurances of the search word Do ' replacement of all occurances r1.Value = Replace(r1.Value, "Pizza", "Burger") ' assigning value again Set r1 = .FindNext(r1) ' the looping / iteration continues only as long as r1 has a value. Loop While Not r1 Is Nothing End If End With End Sub
Output:
Example: Find Exact and Case Sensitive Match
If the search/replace has to affect only the entire cell’s content, then the “LookAt” parameter can be used. This program uses two more parameters: LookAt & MatchCase.
Input data:
Sub FindString_demo2() ' declare 2 variables - one range object and one string Dim myrange As Range Dim Addressofcell1 As String ' with statement is used to avoid repetition of the piece of code in every statement. With Worksheets("Finddemo").Range("A1:E25") ' initiate myrange . xlvalues states that we are looking for matching values. The search word here is "Pizza" Set myrange = .Find("Burger", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) ' check if myrange has a value If Not myrange Is Nothing Then ' if yes , we capture its address Addressofcell1 = myrange.Address ' we enter into a loop that replaces all occurances of the search word Do ' replacement of all occurances myrange.Value = Replace(myrange.Value, "Burger", "Pizza") ' assigning value again Set myrange = .FindNext(myrange) ' the looping / iteration continues only as long as myrange has a value. Loop While Not myrange Is Nothing End If End With End Sub
Output:
“Find and replace” is ignored for other cells that did not fulfill the conditions of the parameters.
Conclusion
The Find function in VBA that can be used to find and replace text using code, can also be used in a customized way to fulfill our needs in the same way the “find and replace” dialog box (the first image of this article) serves us. But as I always say in all my articles, the undo operation is not possible once code is run. So, always perform this operation through a VBA code only for regular or mundane tasks after affirming that the logic in your code is valid and nothing will go wrong.