Applies To
Range Object.
Description
Finds specific information in a range, and returns a Range object that represents the first cell where it is found. Returns Nothing if no match is found. Does not affect the selection or active cell.
For help about using the Find worksheet function in Visual Basic, see "Using Worksheet Functions in Visual Basic" in online Help.
Syntax
object.Find(what, after, lookIn, lookAt, searchOrder, searchDirection, matchCase, matchByte)
object
Required. The range to search.
what
Required. The contents for which you want to search. May be a string or any Microsoft Excel data type.
after
Optional. The first cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that after must be one cell in the range. If this argument is omitted, the top left cell of the range is used as the starting point for the search. Remember that the search begins after this cell; the specified cell is not searched until the method wraps back around to this cell.
lookIn
Optional. One of xlFormulas, xlValues, or xlNotes.
lookAt
Optional. May be xlWhole or xlPart.
searchOrder
Optional. One of xlByRows (to search row-major) or xlByColumns (to search column-major).
searchDirection
Optional. xlNext or xlPrevious; if omitted it is xlNext.
matchCase
Optional. If True, case-sensitive search is performed.
matchByte
Optional. Used only in Far East Microsoft Excel. If True, double-byte characters match only double-byte characters. If False, double-byte characters can match their single-byte equivalents.
Remarks
The settings for lookIn, lookAt, searchOrder, matchCase, and matchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, explicitly set these arguments each time you use this method.
The FindNext and FindPrevious methods can be used to repeat the search.
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
To find cells matching more complicated patterns, use For Each with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font starting with the letters "Cour". When it finds a match, it changes the font to Times New Roman:
For Each c In [A1:C5]
If c.Font.Name Like "Cour*" Then
c.Font.Name = "Times New Roman"
End If
Next
See Also
FindNext Method, FindPrevious Method, Replace Method.
Example
This example finds the first occurrence of the word Phoenix in column B on Sheet1 and then displays the address of the cell that contains this word. If the word is not found, the example diplays a message.
Set foundCell = Worksheets("Sheet1").Columns("B").Find("Phoenix")
If foundCell Is Nothing Then
MsgBox "The word was not found"
Else
MsgBox "The word was found in cell " & foundCell.Address
End If