excelavon

VBA Find Function: Examples, Options, and Common Fixes

VBA & macros · Updated November 3, 2026

VBA Find Function: Examples, Options, and Common Fixes

In Excel VBA, use the Range.Find method to search a range for a value or text. For example, Set foundCell = Worksheets("Orders").Range("A2:A100").Find(What:="INV-1042", LookIn:=xlValues, LookAt:=xlWhole) returns the matching cell, or Nothing if it finds no match. In a macro, check for Nothing before using the result.

A complete VBA Find example

This macro searches cells A2:A100 on a worksheet named Orders for a cell whose value exactly matches INV-1042. It displays the address of the first match, or a message if there is none:

Sub FindExample()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set searchRange = ws.Range("A2:A100")

    Set foundCell = searchRange.Find( _
        What:="INV-1042", _
        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 in " & foundCell.Address(False, False)
    End If
End Sub

Change the worksheet name, range, and search text to suit your workbook. The example uses named arguments so the settings are visible and easier to review than a call that relies on defaults. Before running a macro that changes workbook contents, save a copy: macros cannot be undone with Ctrl+Z. This example only displays a message and does not edit cells.

What Range.Find does

Find is a method of a Range object, rather than a separate VBA function. It searches the range you specify and returns a Range object representing the first matching cell. You can then read or change that cell’s value, inspect its address, or use it as the starting point for further searches.

One important detail is that Find searches only the range you call it on. Calling searchRange.Find limits the search to that range; calling ws.Cells.Find searches the worksheet’s cells. A small, deliberate range can make a macro easier to understand and avoid searching irrelevant parts of a sheet.

The After argument sets the cell after which the search begins. It must be a single cell inside the search range. Excel searches from there and wraps around to the beginning, so specifying the last cell is a convenient way to make the first cell in the range the first candidate. The cell given as After is not checked first; it can be reached later after the search wraps.

Choose the search options deliberately

Several Find settings can change what counts as a match. Setting them explicitly helps make a macro predictable, especially because Excel remembers some Find settings from earlier searches in the user interface and from VBA calls.

  • What is the text, number, or other value to search for.
  • LookIn:=xlValues searches cell values, including calculated results. Use LookIn:=xlFormulas when you want to search the formula text stored in cells.
  • LookAt:=xlWhole requires the whole cell contents to match. LookAt:=xlPart finds the search text anywhere within a cell.
  • SearchOrder:=xlByRows scans across rows before moving down; xlByColumns scans down columns before moving across.
  • SearchDirection:=xlNext searches forward; xlPrevious searches in the opposite direction.
  • MatchCase:=True distinguishes uppercase and lowercase letters. False ignores that distinction.
  • SearchFormat:=False prevents a saved cell-format criterion from narrowing the search.

For one column, the difference between searching by rows and by columns usually has no practical effect. In a rectangular range, however, it changes which match is returned first. If the macro relies on a particular first match, specify the order and direction instead of assuming Excel’s current Find dialog settings.

Find every matching cell

A single Find call returns only one match. To process every matching cell, call FindNext repeatedly and stop when the search returns to the first match. The address check matters because Excel wraps around to the start of the range; without a stopping condition, a loop can run indefinitely.

With the same worksheet and range setup as the first example, this code prints each matching address and value in the Immediate window in the Visual Basic Editor:

Sub FindAllMatches()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set searchRange = ws.Range("A2:A100")

    Set foundCell = searchRange.Find( _
        What:="INV-1042", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then Exit Sub

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

Open the Immediate window in the Visual Basic Editor with Ctrl+G on Windows to see the output. On a Mac, the editor’s menus can be used to show the Immediate window; keyboard shortcuts may differ with the keyboard and macOS settings. The address comparison uses the first match as a stopping point, so every matching cell is visited once even when the search wraps.

Search for partial text and wildcard patterns

Use LookAt:=xlPart to find text contained within a longer cell value. For instance, searching for 1042 with partial matching can find a cell containing INV-1042-REV. With xlWhole, the cell must match the requested value or pattern as a whole.

Find also accepts Excel’s wildcard characters in the search text. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) escapes a wildcard when you need to search for it literally. For example, use What:="INV-*" with whole-cell matching to find cells whose contents begin with INV-. Use What:="~?" with whole-cell matching to look for a literal question mark. Consider the data carefully: a wildcard pattern can match more cells than an exact search.

Case matching is separate from partial matching and wildcards. For example, MatchCase:=True can distinguish Invoice from invoice, while LookAt:=xlPart controls whether the searched text may appear inside a longer value. Set both options according to the task rather than treating them as interchangeable.

Common problems and how to avoid them

The most frequent error is trying to use the result without checking whether a match exists. When there is no match, Find returns Nothing; attempting to read foundCell.Value at that point causes a runtime error. Use an If foundCell Is Nothing Then check before accessing the returned Range.

Another source of inconsistent results is leaving important arguments out. Excel may retain settings such as whether to search formulas or values, whether to match the whole cell, and whether to distinguish case from a previous Find operation. If a macro works for one person but not another, explicit arguments can remove these hidden dependencies.

Also check that the worksheet and range are the ones you intend. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel. If a macro may be run while another workbook is active, qualifying the worksheet through ThisWorkbook can prevent an accidental search of the wrong file. The named worksheet must exist, or the code will stop with an error when it tries to set the worksheet variable.

Finally, remember that xlValues and xlFormulas search different things. A cell displaying a calculated result may contain a formula such as =A1&B1. Search for the displayed result with xlValues; search for formula text with xlFormulas. If the search result seems surprising, inspect the actual cell contents and confirm which kind of content the macro should find.

When Find is better than alternatives

Use Range.Find when you want a cell reference, need partial or wildcard matching, want to search a chosen range, or plan to inspect several matches. It is also useful when the next step is to highlight, read, or edit the matching cell. For changes, take care to define what should happen when there are multiple matches and test on a copy first.

For a straightforward lookup that should return a value from a related table, a worksheet lookup formula may be simpler than a macro. In Microsoft 365, functions such as XLOOKUP can return a related value without VBA. Use Find when the task is specifically to locate a matching cell or when the macro needs to perform follow-up actions. For a comparison involving text already stored in VBA variables, the VBA InStr function may be a better fit because it checks one string inside another rather than searching a worksheet range.

Running and saving a VBA macro

On Excel for Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the code there. If the Developer tab is visible, you can also choose Developer > Visual Basic. To run a macro from the editor, place the cursor inside the procedure and press F5, or return to Excel and run it from Developer > Macros.

On a Mac, the Visual Basic Editor is available from the Developer tab, and Option+F11 may open it; some keyboards require Fn+Option+F11. Menu availability and shortcut behavior can vary with macOS and keyboard settings. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), not as a macro-free .xlsx file, if you need to retain the code. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros; use the desktop application to execute them.

Frequently Asked Questions

What does VBA Find return if it cannot find a match?
It returns Nothing. Check the result with If foundCell Is Nothing Then before trying to use the cell’s value, address, or other properties.

How do I find an exact match in VBA?
Call Find with LookAt:=xlWhole. Also set LookIn to xlValues or xlFormulas, depending on whether you want to search cell values or formula text.

How do I find all matches instead of just the first one?
Call Find once, record the first match’s address, and use FindNext in a loop. Stop when the search returns to that original address, and handle a Nothing result safely.

Can VBA Find search part of a cell?
Yes. Set LookAt:=xlPart. You can also use * and ? as wildcards in the search text, or precede a wildcard with ~ to search for it literally.

Why does Find return a different result than expected?
Check the range, the After cell, search order, and whether the search should use values or formulas. Explicitly set the Find options because Excel can remember settings from earlier searches.

Related

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.