Excel VBA Find Next: Search Matches and Continue Safely

In Excel VBA, use Range.Find to locate the first match and Range.FindNext to continue to the next one. To process every match without looping forever, remember the first cell’s address and stop when the search wraps back to it.

FindNext is useful both for stepping through results and for building a macro that handles all matching cells. The key details are to search a defined range, specify the search options you depend on, and account for the fact that Excel’s search wraps around at the end of that range.

What FindNext Does in VBA

FindNext continues a search previously started with Find. It returns a Range object for the next cell that matches the previous search criteria, or Nothing if there is no match. It does not accept a new search term or new matching options; those come from the preceding Find call.

This is different from calling Find repeatedly with the same search term. Find starts a search based on its arguments, while FindNext advances from a specified cell within that search. For example, a macro can find the first cell containing “invoice,” inspect it, then use FindNext to move to the next matching cell.

Excel wraps the search back to the beginning when it reaches the end of the range. That behavior is helpful when a person clicks Find Next in Excel’s Find dialog, but a macro that loops through matches must detect the wraparound and stop.

A Basic Find and FindNext Example

This macro searches column A on the active sheet for cells whose values contain the word invoice, without regard to capitalization. It prints each matching cell address and value in the Immediate window in the Visual Basic Editor; it does not change worksheet contents.

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

    ' Search a fixed range on the active worksheet.
    Set ws = ActiveSheet
    Set searchRange = ws.Range("A2:A100")

    ' Start after the final cell so the first search begins at the range start.
    Set foundCell = searchRange.Find( _
        What:="invoice", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        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
        If foundCell.Address = firstAddress Then Exit Do
    Loop
End Sub

Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z, even though this particular example only prints results. On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the code there. The Immediate window is available in the editor, commonly with Ctrl+G. Excel for the web cannot run VBA macros; use the desktop Excel app. On Mac, use the desktop app’s Visual Basic Editor, available from the Developer tab when it is enabled.

How the Search Options Affect Results

The options in Find determine what counts as a match. The example sets them explicitly so it is less likely to inherit unexpected choices from a previous Find dialog search or another macro.

What is the text, number, or other value to find. Replace the example’s search term with a variable if the macro should ask for input.
LookIn selects what Excel searches. xlValues searches cell values; xlFormulas searches formula text. A formula cell can therefore behave differently depending on which option you choose.
LookAt sets whether the search must match the whole cell (xlWhole) or can match part of its contents (xlPart). With xlPart, “invoice” can match “Paid invoice 104.”
SearchOrder sets the traversal order, such as by rows (xlByRows) or by columns (xlByColumns).
SearchDirection selects the direction. xlNext moves forward in the chosen order; xlPrevious moves backward.
MatchCase controls whether uppercase and lowercase must match. The example uses False, so “Invoice” and “invoice” are treated alike.
SearchFormat controls whether formatting criteria are used. Setting it to False avoids accidentally carrying a format-based Find setting into this search.

Excel’s Find settings can persist between calls and interact with settings a user chose in the Find dialog. Explicit arguments make a macro’s behavior clearer and more repeatable. If the search needs to match formatting, set up the relevant format criteria deliberately rather than relying on a user’s previous dialog settings.

Why the First Address Matters

A common mistake is to keep calling FindNext until it returns Nothing. If the range contains at least one match, Excel normally wraps to the first result instead of returning Nothing after the last result. The loop can then process the same matches repeatedly.

The example records firstAddress immediately after the initial Find. After each later search, it checks whether the returned cell has that address. If it does, the search has completed a full cycle and the loop exits. The separate Nothing check is still useful defensive code: it handles the case where a subsequent search does not return a range.

Use the address of the first matching cell, not the range’s first cell, as the stopping point. The first match might be halfway down the range. The stopping condition is about completing one full search cycle, not reaching a particular row or column.

Searching for One Match or Every Match

If you need only the next result after a known cell, call FindNext once and check the result before using it. For example, Set foundCell = searchRange.FindNext(After:=currentCell) advances from currentCell, provided that cell belongs to the search range and the relevant search has already been established. If there is no result, test If foundCell Is Nothing Then before reading its address or value.

For every match, use the first-address loop pattern shown above. The work inside the loop can be changed to suit the task: write a result to another range, count matches, or apply a permitted edit. If the macro changes cells that affect whether they match, consider how that change influences later search results. For a task that deletes rows or otherwise changes the search range, collect the matches first or search in an order that does not skip cells as the range shifts.

If the goal is simply to highlight matching values for a person to review, Excel’s Find dialog or conditional formatting may be more suitable than a macro. VBA is most helpful when the action needs to be repeated, applied to a defined range, or combined with other workbook operations.

Define the Range Carefully

Using an explicit range such as A2:A100 makes the macro’s search boundary understandable. Searching an entire worksheet may take longer and can find matches in places that are irrelevant, including notes, headers, or old data far below the visible table. If the data grows, update the range or derive it from a well-defined table or last-used row.

The sample assumes a single, contiguous range on one worksheet. If your data is in an Excel table, you can use the table’s data body range or a specific table column instead of a hard-coded address, adjusting the code to match the actual table name and column. Be cautious with a multi-area, non-contiguous range: search order and results can be less intuitive than with one rectangular range, so a simple loop over separate areas may be easier to reason about.

The sample uses ActiveSheet for a compact demonstration. In a workbook that should always search one particular sheet, set ws to that worksheet explicitly rather than relying on whichever sheet happens to be active. This reduces the risk of searching the wrong sheet when a user changes tabs before running the macro.

FindNext Compared with Excel’s Find Dialog

Excel’s Find dialog is designed for interactive searching: a person enters a term, clicks Find Next, reviews the selected result, and continues as needed. On Windows, Ctrl+F opens Find; on Mac, Command+F is the usual equivalent. The dialog is preferable for an occasional search because it requires no code and lets the user decide what to inspect.

VBA’s FindNext serves a related but more programmable purpose. A macro can repeat the search, record addresses, or perform an operation for each match. Unlike a person stepping through results, the code needs explicit safeguards for no matches and wraparound. It also needs a carefully chosen range and explicit search options so it behaves consistently.

Neither method changes what constitutes a match: options such as whole-cell versus partial matching and values versus formulas still matter. A result that appears surprising is often caused by the search settings, not by FindNext skipping a cell.

Common Problems and Fixes

The macro runs forever: add a first-match address check and exit when the search wraps back to it.
The macro errors when there are no results: test whether the initial Find returned Nothing before accessing properties such as Address or Value.
It finds only part of a cell or misses a partial match: check LookAt. Use xlWhole for a whole-cell match and xlPart for a contained match.
It finds formula text rather than the displayed result, or the reverse: set LookIn to the intended choice, such as xlFormulas or xlValues.
Results change after using Ctrl+F: specify the relevant Find arguments every time instead of relying on settings left by the Find dialog.
The wrong sheet is searched: qualify the range with the intended worksheet instead of relying on ActiveSheet.

Frequently Asked Questions

Does FindNext search the whole worksheet?
It continues the search within the range on which the preceding Find was called. If that range is a column segment, it does not automatically expand to the whole sheet.

Why does FindNext return the first match again?
Excel wraps around to the beginning of the search range after reaching its end. Compare each result’s address with the first match’s address and stop when they are equal.

What should I use for a case-insensitive search?
Set MatchCase:=False in the initial Find call. The example uses this setting, so capitalization does not need to match.

Can FindNext search for a different word?
No. FindNext continues the existing search. To change the term or search options, start a new search with Find.

Can I run VBA FindNext in Excel for the web?
No. Excel for the web can open workbooks containing macros, but it cannot run VBA. Open the workbook in the desktop Excel app to run the macro.

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.