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, FindLookIn, FindLookAt, 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
FindLookIn Optional XlFindLookIn. Specifies whether to search formulas or the displayed value.
XlFindLookIn can be one of these XlFindLookIn constants. |
xlFormulas |
xlValues |
FindLookAt Optional XlFindLookAt. Set this argument to xlWhole to force the entire contents of the cell to match the contents of the What argument.
XlFindLookAt can be one of these XlFindLookAt constants. |
xlPart |
xlWhole |
SearchOrder Optional XlSearchOrder. Specifies whether to search by columns or rows.
XlSearchOrder can be one of these XlSearchOrder constants. |
xlByColumns |
xlByRows |
SearchDirection Optional XlSearchDirection. Specifies the search direction.
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.
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