VBA Find: Search Excel Ranges and Handle Results Safely

In Excel VBA, Find is a method of a Range, not a separate object: it searches a specified range and returns a Range object for the first matching cell, or Nothing if there is no match. Assign the result with Set, and check for Nothing before using it.

What the VBA Find method is

Excel’s object model is organized into objects such as Application, Workbook, Worksheet, and Range. The Find method belongs to Range. In practical terms, you first identify the cells to search, call Find on that range, then work with the returned cell if a match exists.

For example, Range("A1:A100").Find searches only cells A1 through A100. It does not automatically search every sheet or the entire workbook. The return value is a cell reference, so you can inspect its address, read its value, or change the cell. Because the result is an object, VBA requires the Set keyword when you assign it to an object variable.

This distinction helps answer the sometimes ambiguous question “VBA Find object?” If you mean the object returned by a search, it is a Range. If you mean the code element that performs the search, it is the Find method on a Range.

A basic search in a worksheet

This example looks for an exact cell value in column A of the active worksheet. It specifies key options rather than relying on Excel’s remembered Find settings.

Sub FindExactValue()
    Dim ws As Worksheet
    Dim searchArea As Range
    Dim foundCell As Range

    Set ws = ActiveSheet
    Set searchArea = ws.Range("A1:A100")

    ' Search cell values and require a whole-cell match.
    Set foundCell = searchArea.Find( _
        What:="North", _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

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

The macro reports the address of the first matching cell, such as A14. If no cell in the specified range matches, Find returns Nothing. Trying to read foundCell.Value before checking for that result can cause a run-time error. Save a copy of the workbook before running a macro: VBA actions generally cannot be reversed with Ctrl+Z.

Important Find arguments

Find accepts several optional arguments. Named arguments make a call easier to read and reduce the risk of accidentally supplying a value in the wrong position.

What is the text, number, date, or other value to search for.
After sets the cell after which Excel begins searching. It must be a single cell within the search range. Excel begins after that cell, wraps around the range, and checks the After cell last.
LookIn selects what Excel searches. xlValues searches displayed cell values; xlFormulas searches formula content. For instance, a formula cell displaying 25 can be found as a value with xlValues, while searching its formula text with xlFormulas can match the formula itself.
LookAt is either xlWhole for a complete-cell match or xlPart for a match within the cell’s content. Searching for cat with xlPart can match catalog.
SearchOrder sets the scan order: xlByRows or xlByColumns.
SearchDirection is xlNext or xlPrevious, controlling the direction of the search.
MatchCase determines whether uppercase and lowercase letters must match. MatchByte applies to double-byte language support.
SearchFormat controls whether formatting is part of the search. Format searches can be affected by settings in Excel’s Find dialog, so use them deliberately.

Several Find options are retained between calls and can also be influenced by the Find dialog. This is why a macro that omits arguments may behave differently after a user changes a setting in the interface. Supplying LookIn, LookAt, SearchOrder, SearchDirection, and MatchCase explicitly makes the intended search clearer and more consistent.

Find a partial match or search formulas

Use xlPart when the search term may appear inside a longer entry. Use xlWhole when the cell must match the requested item exactly. This matters with names, product codes, and identifiers: a partial search for AB12 might also find AB123, which may be an unwanted match.

Excel Find also treats the asterisk (*) and question mark (?) as wildcard characters. An asterisk can represent any number of characters, while a question mark represents one character. To search for a literal wildcard character, prefix it with a tilde. For example, a search string containing ~* looks for an actual asterisk. This wildcard behavior is separate from the choice between xlWhole and xlPart.

Choose xlValues or xlFormulas based on what you need to match, not simply on whether a cell contains a formula. If you want to find a displayed result, search values. If you need to locate formulas containing a particular expression, search formulas. A formula’s displayed result and its stored formula text are different things, so test the choice against representative cells when a workbook mixes constants and formulas.

Find every matching cell

A single call to Find returns only the first match in the search sequence. To process all matches, call FindNext repeatedly and stop when the search returns to the first found address. The check prevents an endless loop when matches exist.

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

    Set ws = ActiveSheet
    Set searchArea = ws.Range("A1:A100")

    ' Find the first whole-cell match, searching displayed values.
    Set foundCell = searchArea.Find( _
        What:="North", _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    If Not foundCell Is Nothing Then
        firstAddress = foundCell.Address

        Do
            Debug.Print foundCell.Address, foundCell.Value
            Set foundCell = searchArea.FindNext(After:=foundCell)
        Loop While Not foundCell Is Nothing And _
            foundCell.Address <> firstAddress
    End If
End Sub

In this example, matches are printed to the Immediate window in the Visual Basic Editor, which you can show with Ctrl+G on Windows. The code does not change worksheet data. If you adapt the loop to edit cells, inspect the range and intended matches first, and save a separate workbook copy before running it. When searching all matches, keep the original range and search options consistent; changing criteria midway can make the stopping-address logic unreliable.

Choose the right range and worksheet

The range you call Find on defines the search boundary. A targeted range such as ws.Range("A2:A500") is usually easier to reason about than searching every cell on a worksheet. It also helps avoid matches in unrelated notes, headers, or calculation areas.

Qualify the range with a worksheet variable when the macro should search a particular sheet. An unqualified reference such as Range("A1:A100") can refer to the active sheet, which may not be the sheet you intended if the user changes the selection. Similarly, avoid depending on the active workbook when a macro is meant to work with a specific workbook. Explicit references make a procedure less sensitive to what is currently selected.

For a workbook-wide search, loop through the relevant worksheets and search each sheet’s appropriate range. A call to Find on one worksheet range does not automatically continue onto another sheet. Decide whether to include hidden sheets and whether to search whole sheets or only known data columns; those are choices the macro must implement, not automatic workbook-wide behavior of Find.

Common problems and how to diagnose them

No match when one seems to exist: Check whether the search range includes the cell, whether LookIn targets values or formulas as intended, and whether LookAt is too strict. Also check spaces, punctuation, and whether case sensitivity is enabled.
A search finds part of a code unexpectedly: Change LookAt from xlPart to xlWhole when the entire cell must match. If you need a controlled prefix or pattern search, decide explicitly how wildcards should behave.
Run-time error after a search: The usual cause is using a member of the returned range before checking whether the result is Nothing. Put all references to the found cell inside the successful-match branch.
The macro gives different results on different runs: An omitted argument may be using a retained Find setting. Specify the relevant arguments in the call rather than assuming Excel has default settings.
Only one result is processed: Find returns one match per call. Use FindNext with a first-address stopping check when every matching cell is needed.
The wrong sheet is searched: Qualify the range with a worksheet variable. Do not assume the active sheet is always the intended one.

When troubleshooting, inspect the search range, the exact contents of a sample cell, and the chosen arguments. A cell that looks like a number may be stored as text, and a cell showing a calculated result may contain a formula. Those differences can affect what a value-versus-formula search matches.

Find compared with other VBA search options

Range.Find is useful when you need Excel’s familiar search behavior, want to locate a cell, or need options such as whole-cell matching and searching values versus formulas. It is also convenient when the matching cell’s address or other cell properties are needed after the search.

A direct loop through cells can be clearer when the rule is more complex than a text or value search—for example, when several columns must be checked together or each candidate must pass custom conditions. A loop also lets you control comparison logic in VBA, but a poorly designed loop may be slower on a large range. For a simple lookup where you only need a result from a table, a worksheet lookup function may be more concise. Choose based on whether the task is locating a cell, evaluating custom conditions, or returning a related value.

Do not confuse VBA’s Range.Find method with a worksheet formula named FIND. The worksheet function finds the position of one text string within another text string and is case-sensitive; the VBA method searches cells in a range and returns a cell reference. They solve different problems despite sharing a name.

Opening the VBA editor and running a macro

On Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. To insert a standard module, choose Insert > Module in the editor, then paste the procedure into that module. Run a selected procedure with F5 from the editor, or return to Excel and use Developer > Macros to select and run it. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. You can also open it from Excel’s Tools > Macro menu. Ribbon and preference labels can vary slightly by Office release, but the VBA method and its arguments are the same in desktop Excel for Windows and Mac. Excel for the web does not run VBA macros; open the workbook in the desktop application to use this code.

Frequently Asked Questions

Does VBA Find return a cell or a value?
It returns a Range object representing the found cell. Read the cell’s value with a property such as foundCell.Value, after checking that foundCell is not Nothing.

What does Find return when there is no match?
It returns Nothing. Test the object before using properties such as Address or Value so the macro can handle a missing result safely.

How do I make VBA Find match the whole cell?
Set the LookAt argument to xlWhole. Use xlPart only when matching a search term contained within a longer cell entry is acceptable.

How can I find a value on another worksheet?
Set a worksheet variable, then call Find on a range qualified by that variable, such as ws.Range("A1:A100").Find. This avoids relying on whichever worksheet happens to be active.

Why does Find behave differently after I use Excel’s Find dialog?
Excel can retain certain search settings between the dialog and VBA calls. Pass the search arguments explicitly, especially LookIn, LookAt, and SearchOrder, to make the macro’s intended behavior clear.

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.