Range.Find method

ExcelBaby July 27, 2022 6 minutes to read

7 0

Finds specific information in a range.

Tips

Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

Syntax

expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression A variable that represents a Range object.

Parameters

Name	Required/Optional	Data type	Description
What	Required	Variant	The data to search for. Can be a string or any Microsoft Excel data type.
After	Optional	Variant	The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After 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 to this cell. If you don't specify 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 XlFindLookIn constants: xlFormulas, xlValues, xlComments, or xlCommentsThreaded.
LookAt	Optional	Variant	Can be one of the following XlLookAt constants: xlWhole or xlPart.
SearchOrder	Optional	Variant	Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.
SearchDirection	Optional	Variant	Can be one of the following XlSearchDirection constants: xlNext or xlPrevious.
MatchCase	Optional	Variant	True to make the search case-sensitive. The default value is False.
MatchByte	Optional	Variant	Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
SearchFormat	Optional	Variant	The search format.

Return value

A Range object that represents the first cell where that information is found.

Remarks

This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell.

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Use the FindNext and FindPrevious methods to repeat the search.

When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.

To find cells that match more complicated patterns, use a For Each...Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. When Microsoft Excel finds a match, it changes the font to Times New Roman.

For Each c In [A1:C5] If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman" End If Next`

Examples

This example finds all cells in the range A1:A500 in worksheet one that contain the value 2, and changes the entire cell value to 5. That is, the values 1234 and 99299 both contain 2 and both cell values will become 5.

Sub FindValue()
    
    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500") 
        Set c = .Find(2, lookin:=xlValues) 
        If Not c Is Nothing Then 
            firstAddress = c.Address 
            Do 
                c.Value = 5 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing
        End If 
    End With
    
End Sub

This example finds all cells in the range A1:A500 on worksheet one that contain the substring "abc" and then replaces "abc" with "xyz".

Sub FindString()

    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500")
        Set c = .Find("abc", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = Replace(c.Value, "abc", "xyz")
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With

End Sub