Show All

Find Method

Finds specific information in a given range and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Does not affect either the selection or the active cell.

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

FindLookIn   Optional XlFindLookIn. Specifies whether to search formulas or the displayed value.

FindLookAt   Optional XlFindLookAt.  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