Excel VBA Find Method: Syntax, Examples, and Tips

In Excel VBA, use the Range.Find method to search a range and return the first matching cell; if there is no match, it returns Nothing. Specify the search options in the call—especially LookIn, LookAt, and SearchOrder—because Excel can retain settings from earlier searches.

What Excel VBA Find Does

Find is a method of a Range object, not a worksheet formula or a standalone VBA function. You tell it which cells to search and what to look for. It returns a Range reference to a matching cell, which lets your code read the value, change the cell, or act on its row or column.

For example, a search might look for the exact text “North” in column A, then place the corresponding row number in a report. A search that finds nothing does not return an empty string or zero: it returns the special VBA value Nothing. Always test for that result before using the returned cell.

Unlike a worksheet search across an entire sheet, a range search is limited to the range you specify. Limiting the search can make the intended scope clear and avoid accidentally matching a heading, note, or value elsewhere on the sheet.

Basic Syntax and Search Options

The method’s general form is someRange.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat). The arguments after What are optional, but it is good practice to name and set the options that matter. Named arguments make a call easier to review and protect it from inherited Find settings.

What: the value or text to find.
After: a single cell in the range. Searching begins after this cell, so it is not necessarily the first cell checked.
LookIn: what to search, commonly xlValues for displayed cell results or xlFormulas for formulas.
LookAt: xlWhole for a complete-cell match or xlPart for a match within the cell.
SearchOrder: xlByRows or xlByColumns.
SearchDirection: xlNext or xlPrevious.
MatchCase: whether uppercase and lowercase must match exactly.
SearchFormat: whether formatting is part of the search criteria.

For example, LookAt:=xlWhole finds a cell whose contents are exactly “North”; it does not match “North Region.” With LookAt:=xlPart, both cells can match. Setting MatchCase:=False makes the search case-insensitive. Choose the settings to match the data and the task rather than relying on defaults.

A Safe Example: Find One Exact Match

This macro searches A2:A100 on the active sheet for the exact value “North,” searching cell values without regard to case. It reports the address if it finds a match and otherwise gives a clear message.

Sub FindNorth()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim found As Range

    Set ws = ActiveSheet
    Set searchRange = ws.Range("A2:A100")

    ' Search values for a complete-cell match.
    Set found = searchRange.Find(What:="North", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If found Is Nothing Then
        MsgBox "No matching cell was found."
    Else
        MsgBox "Found at " & found.Address
    End If
End Sub

The After argument is set to the last cell in the search range. Because Excel starts searching after that cell and wraps around, this arrangement makes the first cell in the range the first one checked. The cell supplied to After must be within the range being searched. If you use a different starting cell, remember that the search wraps through the range and can still eventually examine cells before it.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even if the macro appears to make only a small edit. The example above only displays a message, but the same precaution is important for macros that change cell contents.

Find Every Matching Cell

Find returns one match at a time. To process all matches, use FindNext and stop when the search returns to the first matching address. Without that stop condition, a loop can circle through the same matches indefinitely because the search wraps around at the end of the range.

This example prints the address and value of every cell in A2:A100 that contains “North” as a complete-cell value. In the Visual Basic Editor, open the Immediate window with Ctrl+G to view the output on Windows.

Sub FindAllNorthMatches()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim found As Range
    Dim firstAddress As String

    Set ws = ActiveSheet
    Set searchRange = ws.Range("A2:A100")

    ' Start after the last cell so the first cell is checked first.
    Set found = searchRange.Find(What:="North", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

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

The first-address check is important even when the range is small: a successful search that reaches the end normally wraps to the first result. The loop also checks for Nothing before reading the next cell. Keep the range and search options consistent when calling FindNext; it continues the previous search rather than defining a completely new one.

Searching Values, Formulas, and Partial Text

The choice between xlValues and xlFormulas can change which cells match. If a cell contains a formula that evaluates to “North,” searching values looks at the calculated result. Searching formulas looks at the formula text stored in the cell. Use values when you care about what the worksheet displays as its result; use formulas when you need to inspect the underlying formula.

For a partial match, set LookAt:=xlPart. This can help find a keyword inside longer descriptions, but it can also create unintended matches—for example, a search for “art” could match “department.” For identifiers, product codes, or category labels, whole-cell matching is often the safer choice.

Excel’s Find method also supports wildcard searches. An asterisk represents a string of characters, and a question mark represents one character. To search for a literal asterisk, question mark, or tilde, prefix it with a tilde in the search text. Wildcards are useful for variable text patterns, but a broad wildcard can match many cells; test it on a limited range before using it in a macro that changes data.

Search Order, Direction, and Case

SearchOrder:=xlByRows searches across rows before moving to the next row, while xlByColumns searches down columns before moving to the next column. This affects which match is returned first when the range contains multiple matches. It does not change which cells are eligible to match.

SearchDirection:=xlNext moves forward through the chosen order; xlPrevious moves backward. Because the search wraps around, “next” does not mean that the method stops at the end of the range. If your task requires the earliest match in a particular row or column order, set both the order and direction deliberately and choose the After cell to establish a predictable starting point.

With MatchCase:=False, “north,” “North,” and “NORTH” can match the same search text. Use True when capitalization distinguishes values in the data. VBA’s Find settings do not make otherwise different text identical; spelling, spaces, and punctuation can still determine whether a match is found.

Why Find Can Return Unexpected Results

A frequent source of confusion is Excel retaining Find options. Settings used in a previous VBA search or in Excel’s Find dialog can affect later searches when an argument is omitted. A user may have searched formulas, partial contents, or formatted cells in the interface, for instance. Supplying the important arguments explicitly makes a macro more predictable and easier to maintain.

Other useful checks include:

Confirm the range: make sure the code is searching the intended worksheet and cells, not whichever sheet happens to be active.
Check values versus formulas: a formula result and the formula text are different search targets.
Check exact versus partial matching: xlWhole and xlPart can produce very different results.
Look for extra spaces or different punctuation: values that look similar on screen may not contain identical text.
Check formatting criteria: if format searching is enabled, a cell may fail to match because its formatting differs.
Handle no match: test whether the returned object is Nothing before reading its properties.

For a search that unexpectedly returns the wrong occurrence, inspect the order, direction, and After cell. For a search that finds nothing, first test a smaller known range and make the match settings explicit. Merged cells or unusual worksheet layouts can also make results less intuitive; a simple, clearly defined search range is easier to reason about.

Range.Find Compared with FIND and SEARCH

Excel’s worksheet FIND and SEARCH functions are not the same as VBA’s Range.Find method. The worksheet functions look for one text string inside another and return a character position. FIND is case-sensitive, while SEARCH is not. By contrast, Range.Find searches cells and returns the matching cell’s range reference, which VBA can use to navigate or make changes.

For a worksheet cell formula that needs the location of a substring within text, use the relevant worksheet function. For a macro that needs to locate a cell in a defined range, use Range.Find. In VBA, InStr is another option when the goal is to locate text within a string already held in a variable rather than search a worksheet range.

Running VBA in Microsoft Excel

These examples are written for Excel for Microsoft 365 on Windows. To add one, save the workbook as an Excel Macro-Enabled Workbook (.xlsm), press Alt+F11 to open the Visual Basic Editor, and choose Insert > Module. Paste the code into the module, return to Excel, and run the macro from the Macro dialog or the editor. Use macros only in workbooks and files you trust.

On a Mac, VBA is also available in the desktop Excel app, though keyboard shortcuts and some interface details differ. The Visual Basic Editor can generally be opened with Option+F11, sometimes with the Fn key depending on the keyboard. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros; use desktop Excel for those tasks.

For a macro that modifies cells, save a separate copy first: Ctrl+Z on Windows and Command+Z on Mac do not undo VBA macro actions. Also consider qualifying worksheet references explicitly, as the examples do with a worksheet variable, so the code does not silently search whichever sheet is active when it runs.

Frequently Asked Questions

Why does VBA Find return Nothing?
It returns Nothing when no cell in the specified range matches the search text under the chosen settings. Check the range, whether you need xlValues or xlFormulas, and whether the match should be whole-cell or partial. Always test for Nothing before using the result.

How do I make VBA Find search the whole worksheet?
Set the search range to the worksheet’s cells, such as ws.Cells, and specify the options explicitly. Searching every cell can be slower and may include areas beyond the table you care about, so a defined data range is usually more precise.

How do I find all occurrences, not just the first?
Use Find for the first match, record its address, and repeatedly call FindNext. Stop when the next result is Nothing or its address returns to the first match, as in the loop above.

Is VBA Find case-sensitive?
Set MatchCase:=True for a case-sensitive search and MatchCase:=False for a case-insensitive one. State the setting in the call instead of relying on a previous Find dialog or macro setting.

Can Excel for the web run a VBA Find macro?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Microsoft 365 on Windows or Mac to run VBA; the web app can still be used for many ordinary workbook tasks.

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.