Find Method

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

expression    An expression that returns a Range object.

What    Required Variant. The data to be searched for.

After    Optional Variant. Specifies a single cell after which the search begins. This corresponds to the position of the active cell when a search is done from the user interface. 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 XlFindLookIn . Specifies whether to search formulas or the displayed value.

LookAt    Optional XlLookAt . Set this argument to xlWhole to force the entire contents of the cell to match the contents of the What argument.

SearchOrder    Optional XlSearchOrder . Specifies whether to search by columns or rows.

SearchDirection    Optional XlSearchDirection . Specifies the search direction.

MatchCase    Optional Boolean. True to make the search case sensitive. The default value is False.

MatchByte    Optional Variant. Used only if you’ve 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.

Example

This example finds all occurrences of "Mike" in the range A1:F10 and makes those cells bold.

Sub Find_Mike()
    Dim ssConstants
    Dim rngFindRange
    Dim rngFoundCell
    Dim rngFirstFound

    Set ssConstants = Spreadsheet1.Constants

    ' Set a variable to the range to search.
    Set rngFindRange = Spreadsheet1.Sheets("Sheet1").Range("A1:F10")

    ' Find the first occurrence of Mike.
    Set rngFoundCell = rngFindRange.Find("Mike", rngFindRange.Cells(1, 1), _
                    ssConstants.xlValues, ssConstants.xlPart)

    ' If Mike was found...
    If Not rngFoundCell Is Nothing Then

        ' Set a variable to the first found instance.
        Set rngFirstFound = rngFoundCell

        Do
            ' Set the font to bold.
            rngFoundCell.Font.Bold = True

            'Find the next occurrence of Mike.
            Set rngFoundCell = rngFindRange.FindNext(rngFoundCell)

        ' Loop until you return to the first occurrence of Mike.
        Loop Until rngFoundCell.Address = rngFirstFound.Address
    End If
End Sub