FindNext Method

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn’t affect the selection or the active cell.

expression.FindNext(After)

expression   Required. An expression that returns a Range object.

After  Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell in the user interface. Note that After must be a single cell in the range. Remember that the search begins after the active cell; the active cell itself isn’t searched until the FindNext method wraps back around to the active cell. If this argument isn’t specified, the search starts after the cell in the upper-left corner of the range.

Example

This example finds all occurrences of "Mike" in Sheet1 and makes those cells bold.

  Sub FindMike()

    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").UsedRange

    ' Find the first ocurence 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