VBA Find Method in Excel: Syntax and Examples

In Excel VBA, the Range.Find method searches a specified range and returns the first matching cell as a Range object; if nothing matches, it returns Nothing. Set the important search arguments explicitly—especially LookIn, LookAt, and SearchOrder—because Excel can retain Find settings from earlier searches.

What the VBA Find method does

Find is useful when a macro needs to locate a value or text without checking every cell one by one. You call it on a range, supply what to look for, and receive a reference to the cell where Excel found a match. That reference can be used to read the cell, change its formatting, update its value, or determine its address.

The search is limited to the range you call it on. For example, searching Sheet1.Range("A2:A100") will not inspect column B, another worksheet, or cells below row 100. To search a whole worksheet, use that worksheet’s Cells range, but remember that a whole-sheet search can take longer and may match content far from the area you intended.

Find searches in a circular sequence. After reaching the end of the range, Excel can continue from the beginning. That behavior matters when you search for every match: a loop must stop when it returns to the first address, or it can run indefinitely.

Syntax and the arguments that matter

The method has several optional arguments. A reliable call supplies the ones that affect the result rather than relying on Excel’s remembered settings:

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What is the text, number, or other value to find.
After is one cell within the search range. The search starts after that cell and eventually wraps around to it.
LookIn determines what Excel searches, such as cell values (xlValues) or formula contents (xlFormulas).
LookAt sets exact-cell matching with xlWhole or partial matching with xlPart.
SearchOrder sets the traversal order: across rows with xlByRows, or down columns with xlByColumns.
SearchDirection sets forward search with xlNext or backward search with xlPrevious.
MatchCase controls whether uppercase and lowercase letters must match exactly.
SearchFormat controls whether formatting criteria are part of the search.

MatchByte applies to double-byte language support and is generally not needed for ordinary English-language searches. Excel’s VBA documentation lists it as an optional argument, but most macros can omit it.

Find one match: a complete example

This macro looks for the exact cell value Mira in A2:A100 on Sheet1. It reports the address if it finds a match and displays a message if it does not. Before running any macro, save a copy of the workbook: macro actions cannot be reversed with Ctrl+Z.

Sub FindOneMatch()
    ' Search for an exact value in a defined range.
    Dim searchRange As Range
    Dim foundCell As Range

    Set searchRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")
    Set foundCell = searchRange.Find( _
        What:="Mira", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No match was found."
    Else
        MsgBox "Found at " & foundCell.Address(False, False)
    End If
End Sub

Here, After is the last cell in the search range, so the search begins after that cell and wraps to the start. This makes A2 the first cell checked. The argument must refer to a single cell inside the range; it is not a way to exclude that cell from a complete search.

The example uses ThisWorkbook, which means the workbook containing the VBA code. If the macro should search the workbook currently active in Excel instead, ActiveWorkbook is another option, but it can point to a different file if the user switches workbooks.

Choose what counts as a match

The same text can produce different results depending on the search settings. If the range contains “Mira Patel” and “Mira,” LookAt:=xlWhole finds only the cell whose full contents match Mira. LookAt:=xlPart can find both because Mira appears within each cell’s contents. Use exact matching for identifiers, category labels, and codes when a partial hit could be misleading.

LookIn also changes the meaning of a search. With xlValues, Excel looks at displayed/evaluated cell values; with xlFormulas, it searches formula contents. For example, if a cell contains =A1&"-West", searching for text that appears in the formula itself may require xlFormulas, while searching for the calculated result calls for xlValues. A cell’s number format can affect what is displayed, so a date or formatted number may not look the same as the underlying stored value.

MatchCase:=False makes the search case-insensitive; set it to True when capitalization is significant. The search order affects which match is returned first, not which cells qualify. For example, with xlByRows, Excel checks across a row before moving to the next row; with xlByColumns, it moves down a column before moving to the next.

Find every occurrence without looping forever

To process multiple matching cells, call Find once, save the first address, and use FindNext until the search returns to that address. Because results wrap around, checking only whether a match exists is not a sufficient stopping condition. This example prints the addresses of every exact match in a range:

Sub FindAllMatches()
    ' Print the address of each exact match, once.
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set searchRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")
    Set foundCell = searchRange.Find( _
        What:="Mira", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If Not foundCell Is Nothing Then
        firstAddress = foundCell.Address
        Do
            Debug.Print foundCell.Address(False, False)
            Set foundCell = searchRange.FindNext(After:=foundCell)
            If foundCell Is Nothing Then Exit Do
        Loop While foundCell.Address <> firstAddress
    End If
End Sub

Open the VBA editor’s Immediate window to view the addresses printed by Debug.Print. The loop tests for Nothing before it reads the next address, avoiding an error if the search returns no cell. When you adapt the loop to change values or delete rows, consider whether those changes could alter the range or the matches while the search is in progress.

Wildcards and formatting searches

Excel’s Find method accepts wildcards in the What argument. An asterisk (*) represents any sequence of characters, and a question mark (?) represents one character. For example, What:="North*" with LookAt:=xlPart can match text beginning with North. To search for an actual asterisk or question mark rather than use it as a wildcard, prefix it with a tilde: use ~* for a literal asterisk and ~? for a literal question mark. A literal tilde is written as ~~.

Format-based searches are possible, but they require additional care. Set a format criterion through Application.FindFormat, then call Find with SearchFormat:=True. For example, a macro can search for cells with bold font. Clear old format criteria with Application.FindFormat.Clear before setting new criteria so a previous Find operation does not affect the result. Format-search state, like other Find settings, can interact with searches performed elsewhere in Excel.

When the goal is simply to find a value, use SearchFormat:=False. That makes the intent explicit and prevents an earlier format search from unexpectedly excluding cells. For repeatable automation, specifying all relevant search options is safer than depending on the Find dialog’s previous state.

Common problems and practical safeguards

The result changes between runs: Excel remembers several Find options between calls and between VBA and the Find dialog. Specify the options your code depends on every time.
The macro errors when no match exists: A failed search returns Nothing. Test with If foundCell Is Nothing Then before using properties such as Address or Value.
A partial match appears unexpectedly: Choose xlWhole for a complete-cell match rather than xlPart.
The wrong duplicate is returned: Check After, SearchDirection, and SearchOrder. These determine where the search starts and which qualifying cell comes first.
A formula is not found: Decide whether the search should examine the formula text or its evaluated value, and set LookIn accordingly.

Find is not a visibility filter. A range search can include cells in hidden rows or columns; filtering a table does not by itself make Find search only the visible records. If a macro must act only on visible cells, that requirement needs separate handling. Also be careful with merged cells: their layout can make it less obvious which cell in a range actually holds the content.

When to use Find instead of other methods

Use Find when you need the location of a match, particularly when you do not know which row contains it or want to search a bounded range efficiently. If you already know the row and want to return a related value, a worksheet lookup formula may be clearer. In Microsoft 365, functions such as XLOOKUP can return a corresponding value without writing a search loop; they do not replace Find when VBA needs a cell reference to format or edit.

A simple VBA loop through each cell may be easier to understand when applying complex custom rules, such as comparing normalized text or handling several conditions at once. However, a cell-by-cell loop over a very large range can be slower. Another option is to read a range into a VBA array, process the values in memory, and write results back, which is useful for larger data-processing tasks but requires more code.

For a human doing a one-time search, Excel’s Find dialog is often enough: press Ctrl+F on Windows or Command+F on Mac. The dialog and VBA both use Excel’s search concepts, but VBA is appropriate when the search needs to be repeated or incorporated into a macro. VBA macros run in desktop Excel, including the Windows and Mac apps; Excel for the web does not run VBA macros.

Frequently Asked Questions

What does Range.Find return if it finds nothing?
It returns Nothing, not an empty string or zero. Check the object before trying to access its address, value, or other properties.

Does VBA Find search formulas or their results?
It depends on LookIn. Use xlFormulas to search formula contents and xlValues to search evaluated values.

How do I find an exact match in VBA?
Set LookAt:=xlWhole. Add MatchCase:=True if capitalization must also match exactly.

Why does Find return a different cell than expected?
Check the search range, the After cell, the search direction and order, and the remembered Find options. Explicit arguments make the starting point and match rules predictable.

Can I use VBA Find in Excel for the web?
No. Excel for the web can open a workbook containing VBA, but it cannot run VBA macros. Use desktop Excel to run the code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.