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:

1. Search within the active sheet alone/the entire workbook
2. Search by rows/columns
3. 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:

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.