VBA Find and Replace: Methods, Examples, and Tips in Excel

In Excel VBA, use Range.Find to locate matching cells and Range.Replace to change matching content. For example, Worksheets("Data").Range("A1:D100").Replace What:="pending", Replacement:="complete", LookAt:=xlWhole replaces whole-cell matches in that range; set the search options explicitly so the macro behaves predictably.

What VBA Find and Replace Does

Excel’s Find and Replace tools have two distinct VBA counterparts. The Find method returns a Range object for a matching cell, which lets your code inspect the cell, select it, or take another action. The Replace method changes matching content in the range you call it on. Use Find when you need to make decisions based on what was found; use Replace when a direct, consistent text substitution is enough.

Neither method is automatically limited to the current worksheet or the whole workbook. The range you supply determines the scope. A call on Range("A1:D100") searches or replaces within those cells; a call on Cells can cover the worksheet. To cover multiple worksheets, your code must loop through them or specify each range separately.

These methods are useful for routine cleanup, updating labels, correcting repeated text, and finding cells that meet a search condition. They are not a general-purpose regular-expression engine. Excel Find supports wildcard characters, but its matching rules are simpler than regular expressions.

Replace Text in a Specific Range

This example replaces cells whose entire content is pending with complete in a defined range. It uses named arguments to make the matching rules clear.

Sub ReplaceStatus()
    Dim ws As Worksheet
    Dim target As Range

    ' Save a copy of the workbook before running this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("A1:D100")

    target.Replace What:="pending", Replacement:="complete", _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
        MatchByte:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Change the worksheet name and address to match your workbook. xlWhole means the cell content must match the search text as a whole. If you instead use xlPart, a cell containing “pending review” can match “pending,” so the replacement may produce “complete review.” Partial matching can be convenient, but it can also change words or phrases you did not intend to edit.

The Replace method returns a Boolean value indicating whether a replacement was made, but the example above does not use that return value. If you need to report whether anything changed, assign the result to a declared Boolean variable and display or log it. A no-match result is not necessarily an error: it may simply mean the search text was absent from the specified range.

Find a Match and Process It

To locate a cell without changing it, call Find. The example below searches displayed cell values in a range and prints each matching cell’s address and value to the Immediate window in the Visual Basic Editor. It continues until it reaches the first match again, which prevents an endless loop when the search wraps around.

Sub ListInvoiceMatches()
    Dim ws As Worksheet
    Dim searchArea As Range
    Dim hit As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchArea = ws.Range("A1:D100")

    With searchArea
        Set hit = .Find(What:="invoice", _
            After:=.Cells(.Cells.Count), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, _
            MatchByte:=False, SearchFormat:=False)

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

Open the Immediate window in the Visual Basic Editor to see the output; it is a diagnostic window, not a worksheet report. You can replace the Debug.Print line with code that records addresses, checks another column, or applies a separate action. Be cautious about selecting or changing cells inside a Find loop: changing the searched content can affect which later cells match.

Choose the Search Options Carefully

The options passed to Find control what counts as a match and where the search proceeds. Specify the important ones rather than relying on defaults, because Excel remembers some Find settings between calls and shares settings with the Find dialog.

What is the text, number, or other search value.
LookIn determines whether Excel searches values, formulas, or notes. Use xlValues when you want to search the values shown in cells, and xlFormulas when the formula text itself matters.
LookAt is xlWhole for a full-cell match or xlPart for a substring.
SearchOrder chooses row-by-row (xlByRows) or column-by-column (xlByColumns) order.
SearchDirection is usually xlNext to move forward or xlPrevious to move backward.
MatchCase controls whether uppercase and lowercase letters must match exactly.
SearchFormat controls whether formatting criteria are included. Set it to False for a text-only search.

In the example, After identifies the cell after which Excel starts. It must be a single cell within the search range. Starting after the range’s last cell is a common way to make the first search examine the first cell, while still allowing Excel’s normal wraparound behavior. The starting point does not mean Excel permanently skips that cell.

Whole-Cell Matches, Partial Matches, and Wildcards

Choosing xlWhole versus xlPart is one of the most consequential decisions. Suppose a range contains “cat,” “catalog,” and “copy.” Searching for “cat” with xlWhole matches only the cell containing exactly “cat”; with xlPart, it can also match “catalog.” The same distinction matters for replacements: a partial replacement can modify a longer label or a phrase embedded in a cell.

Excel Find also recognizes * as a wildcard for any number of characters and ? for one character. To search for a literal asterisk or question mark, precede it with a tilde, such as ~*. A tilde itself is escaped by doubling it. Wildcards are useful for patterns such as names beginning with a prefix, but test the pattern on a small range first. A broad wildcard combined with xlPart can match far more cells than expected.

Case-insensitive matching is the default in many ordinary Find operations, but code should declare MatchCase explicitly. For example, if a workbook distinguishes “ID” from “id,” set MatchCase:=True. If case is not meaningful in the data, False is usually the more inclusive choice.

Search a Worksheet or Multiple Worksheets

To search an entire worksheet, use that worksheet’s Cells range instead of a limited address. That can be practical for a one-off search, but it is often better to narrow the range when the workbook is large or only certain columns contain relevant data. A defined range makes the macro’s intent clearer and reduces the chance of changing unrelated notes or formulas.

For multiple worksheets, loop over the workbook’s worksheets and run the same operation on each eligible sheet. Decide whether hidden sheets should be included and whether every sheet has the same layout. A broad workbook-wide replace can change titles, instructions, formula text, or archived data as well as the intended entries. If only a specific column should change, build the target range separately for each sheet instead of replacing across every cell.

Also choose the workbook reference deliberately. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel. If code is stored in an add-in or personal macro workbook, those can be different workbooks. Using the wrong reference can make a macro act on a different file than the one the user expects.

Common Problems and How to Avoid Them

Unexpected matches: Check whether the code uses xlPart rather than xlWhole, and whether case matching or wildcards broaden the search.
Different results on repeated runs: Explicitly set arguments such as LookIn, LookAt, search order, case matching, and format matching. Excel’s Find settings can persist between uses.
No result even though text appears visible: The cell may contain a formula. Search with LookIn:=xlValues to look at its displayed result, or LookIn:=xlFormulas to look at the formula string.
Only one match is handled: Find returns one match. Use FindNext and stop when the search returns to the first address, as in the loop above.
Macro errors on a missing match: Test whether the returned range is Nothing before reading its address or value. A missing match is a normal outcome.
More cells change than expected: Restrict the target range, use whole-cell matching when appropriate, and test on a copy before running the macro on important data.

One subtle issue is that VBA’s And operator does not short-circuit. In a loop condition such as Not hit Is Nothing And hit.Address <> firstAddress, VBA may still evaluate hit.Address when hit is Nothing. The sample avoids that risk by checking for Nothing in a separate statement before testing the address.

Run the Macro Safely in Excel

VBA macros can change many cells quickly, and Excel’s Undo command does not undo changes made by a macro. Save a separate copy of the workbook before running any Find and Replace macro, especially one that uses Cells or loops through worksheets. For a first run, use a small test range or a duplicate workbook and inspect the result before widening the scope.

On Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. Add code in a standard module, then run the macro from the editor or from Excel’s macro dialog. Save a macro-enabled workbook as an .xlsm file if you need to retain its VBA code.

Excel for Mac desktop also supports VBA, though keyboard shortcuts and security prompts can differ; use the Visual Basic Editor command in Excel’s menus if the Windows shortcut does not apply to your keyboard. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop application to execute this code. Only enable macros from sources you trust.

When to Use the Excel Interface Instead

For a one-time manual change, Excel’s Find and Replace dialog is usually simpler: press Ctrl+H on Windows or use Home > Find & Select > Replace. In Excel for Mac, use the Find and Replace command available from Excel’s menus; the precise menu presentation can vary by version. The dialog makes it easier to review the scope and options interactively before changing cells.

VBA is a better fit when the same task must be repeated, when it depends on conditions, or when it must be applied across several ranges or worksheets. For structured transformations that need repeatable import and cleanup steps, Power Query may be a more suitable alternative than a macro. The choice depends on whether you need a one-off edit, a reusable procedure, or a refreshable data transformation.

Frequently Asked Questions

How do I replace text in an entire worksheet with VBA?
Use the worksheet’s Cells range as the target and call its Replace method. Set the matching options explicitly, and test on a copy because a worksheet-wide replacement can affect unrelated content.

How do I find every occurrence instead of just the first?
Call Find once, save the first match’s address, and use FindNext in a loop. Stop when the result is Nothing or the search returns to the first address; otherwise, Excel’s wraparound can make the loop repeat.

Does VBA Find search formulas or displayed values?
Set the LookIn argument. xlValues searches cell values as displayed, while xlFormulas searches formula text. This distinction explains why a visible result may not match a search for the formula itself.

Can I use VBA Find and Replace in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in Excel desktop for Windows or Mac to use VBA; the web version can still be used for ordinary spreadsheet editing.

Can I undo a VBA replacement with Ctrl+Z?
No. Excel’s regular Undo history does not reverse changes made by a VBA macro. Save a backup copy before running the procedure, or write the macro to record original values if the task requires a custom recovery method.

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.