What is the syntax to search a range?

Range("<your range here>").Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
'What - data to search for; only mandatory parameter
'value (ex) any VBA data type like 123, 12.3, "name"

'After - a single cell to start searching from
'value (ex) Range("A1"), Range("B5")

'LookIn - to search in formulas, values, or comments
'value (ex) xlValues, xlFormulas, xlComments

'LookAt - look at part of cell or entire cell?
'value (ex) xlWhole, xlPart

'SearchOrder - search by rows or columns
'value (ex) xlByRows, xlByColumns

'SearchDirection - search next cell or previous ones
'value (ex) xlNext, xlPrevious

'the following parameter's values are either true or false
'MatchCase - is search case sensitive?
'MatchByte - only relevant if you have installed double-byte language support
'SearchFormat - search by formatting (which is set using Application.FindFormat)

It is critical to note that the Range.Find method does not return a value, rather, it returns a Range object. If nothing is found, the Range object will be Nothing.


'returns Emily Carn

'selects cell B8
Range("B3:B20").Find(What:="Emily", MatchCase:=False).Select

'returns 19
Range("B3:B20").Find(What:="Emily", After:=Range("B17")).Row



What is the syntax to return the row of a search value?

<integer variable> = Application.WorksheetFunction.Match(<search value>, Range("<searched range index>"), <0 for exact match or 1 for approximate>)

Helpful tip: also use error handlers to catch 1004 errors (that arise from not finding a match for your search value)

On Error GoTo ErrorHandler
foundRow = Application.WorksheetFunction.Match(“James Robert”, Range(“A1:A100”), 0)
 MsgBox “Name not found”
Resume Next

'example - find a name on a sheet, then delete the row containing name
On Error Resume Next
foundRow = Application.WorksheetFunction.Match("JamesRobert", Range("B2:B30"),0)