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.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
XlFindLookIn
XlFindLookIn can be one of these XlFindLookIn constants. |
xlFormulas |
xlValues |
XlLookAt
XlLookAt can be one of these XlFindLookAt constants. |
xlPart |
xlWhole |
XlSearchOrder
XlSearchOrder can be one of these XlSearchOrder constants. |
xlByColumns |
xlByRows |
XlSearchDirection
XlSearchDirection can be one of these XlSearchDirection constants. |
xlNext |
xlPrevious |
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
Applies to | Range Object
See Also | FindNext Method | FindPrevious Method