Excel VBA Find and Replace in a Range: Code and Tips

To find and replace text only within a chosen Excel range, use VBA’s Range.Replace method and call it on that range—not on the whole worksheet. For example, Range("A2:D100").Replace What:="Pending", Replacement:="Queued", LookAt:=xlWhole targets those cells; the complete example below sets the other important options explicitly.

What range-based replacement does

Excel’s VBA Range.Replace method searches cells in the range you specify and replaces matching text. The key to keeping a change contained is the object before .Replace. If that object is Worksheets("Data").Range("A2:D100"), the operation is restricted to A2:D100 on the Data sheet. If it is Cells or a whole worksheet, the search area is much larger.

Replacement can be useful for normalizing labels, correcting repeated typos, updating a status word, or changing a text fragment in a controlled block of cells. It is not the same as changing one cell’s value with an assignment: Replace searches for a specified string and changes matching content according to its matching options. A range may contain constants, formulas, or both, so consider what is in the target cells before running it.

What: the text or fragment to search for.
Replacement: the new text to put in place of a match.
LookAt: whether the search text must match the whole cell or can match part of it.
MatchCase: whether uppercase and lowercase letters must match exactly.

A complete VBA example

This macro replaces cells whose entire contents are Pending with Queued in A2:D100 on a worksheet named Data. Change the sheet name, range, and words to fit your workbook. The example uses a workbook-qualified reference, so it does not depend on which sheet happens to be active.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep its VBA code.

Sub ReplaceTextInData()
    Dim target As Range
    Dim didReplace As Boolean

    ' Limit the operation to this worksheet range.
    Set target = ThisWorkbook.Worksheets("Data").Range("A2:D100")

    ' Replace whole-cell matches, ignoring letter case.
    didReplace = target.Replace(What:="Pending", Replacement:="Queued", _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
        MatchByte:=False, SearchFormat:=False, ReplaceFormat:=False)

    If didReplace Then
        MsgBox "At least one replacement was made."
    Else
        MsgBox "No matching cells were replaced."
    End If
End Sub

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure, and run it with F5 while the cursor is inside it. You can also close the editor and use Alt+F8 to select and run the macro. On a Mac, use Option+F11 to open the editor (some keyboards require Fn as well); Option+F8 opens the macro dialog. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros. Use desktop Excel for those tasks.

Choose the right range and worksheet

Use a fixed address when the data area is known and stable. A reference such as Range("A2:D100") is easy to inspect and reduces the chance of changing unrelated cells. If the list grows regularly, consider placing the data in an Excel Table and identifying its data column in VBA, or calculate the last used row carefully. A fixed address that stops at row 100 will not include new records below that row.

Qualify both the worksheet and workbook when the macro might be run from another workbook or while a different sheet is active. ThisWorkbook means the workbook that contains the VBA code. By contrast, ActiveWorkbook means the workbook currently in focus, which can change while a user works. A sheet name must match the worksheet tab name exactly. If the named sheet does not exist, VBA raises an error rather than silently choosing another sheet.

For a one-off operation, a selected range can be used, but it is more dependent on the user’s current selection. If using Selection.Replace, confirm the selection first; a click in the wrong sheet or a selected entire column can make the operation broader than intended. A named, explicit range is usually easier to audit and repeat.

Whole-cell matches versus partial matches

Set LookAt:=xlWhole when the cell must contain exactly the search text to qualify. In the example, a cell containing Pending is replaced, but a cell containing Pending approval is not. This is generally the safer choice for categories, codes, and status labels when you intend to change a complete value.

Use LookAt:=xlPart when the search phrase should be replaced wherever it appears inside a cell. For instance, replacing colour with color can change text such as colour palette to color palette. Partial matching can also alter words or identifiers you did not mean to touch. Searching for cat, for example, could match that sequence inside a longer word. Inspect the possible matches or test on a copy before using a broad fragment.

MatchCase:=False treats uppercase and lowercase variants as equivalent for matching; MatchCase:=True requires the case to match. Matching case does not mean the replacement will preserve each match’s original capitalization: the replacement text you provide is what Excel inserts. If mixed-case replacements matter, use separate rules or custom VBA logic.

Replace versus Find and FindNext

Use Range.Replace when the goal is to make the same change to every qualifying match in a range. Its Boolean return value indicates whether a replacement was made, which is useful for a basic confirmation message. It does not provide a list of every changed cell, so it is not the best choice when you need to review each result individually before deciding what to change.

Use Range.Find when you want to locate a match, inspect its address or value, and then decide what to do. Find returns a Range object for a match, or Nothing when no match is found. To locate multiple matches, VBA code typically records the first match’s address and calls FindNext until the search returns to that address. This prevents an endless loop because Excel’s search wraps around to the beginning of the range.

Find and Replace have different argument lists. Find has a LookIn option for searching values or formulas; Replace does not take a LookIn argument. Both methods have settings such as LookAt and MatchCase. Set relevant arguments explicitly in VBA because several Find and Replace settings are remembered between calls and can also reflect settings used in Excel’s Find dialog.

Formulas, formats, and search settings

Be cautious when the target range includes formula cells. Replacement can affect text in formulas as well as ordinary cell contents, depending on the matching text and operation. For example, a partial replacement aimed at a word that appears inside a formula may change the formula text, not merely its displayed result. Restrict the target to a values-only data column when that is your intention, and test the procedure on a workbook copy if formulas are in scope.

The example supplies SearchFormat:=False and ReplaceFormat:=False to avoid relying on a saved search-format criterion or requesting a formatting replacement. These options help make the operation more predictable, but they do not determine whether the target includes formulas. SearchOrder:=xlByRows specifies row-wise search order; for a simple replace-all action it usually does not affect which cells match, but setting it explicitly avoids inheriting an old setting.

MatchByte is included because it is an argument of the method; it is chiefly relevant to double-byte language support. For typical English-language workbooks, the more consequential choices are the range, LookAt, MatchCase, and whether format matching is enabled.

Common problems and safer troubleshooting

Nothing changed: verify the sheet name, range address, search text, and whether xlWhole is too strict. A cell with extra spaces or additional words is not an exact whole-cell match.
Too much changed: check whether xlPart was used, whether the range is larger than intended, and whether the search string appears inside formulas or longer words.
Results differ between runs: explicitly provide the Replace arguments instead of relying on remembered Find and Replace dialog settings.
Run-time error on the sheet reference: check the tab name and confirm the macro is stored in the workbook expected by ThisWorkbook.
Macro will not run: confirm the file is saved in a macro-enabled format and that macros are allowed by your organization’s security settings. Do not enable macros in an untrusted file just to run its code.

Before a large replacement, test the code on a small range or a duplicate workbook. You can also use Excel’s Home > Find & Select > Replace command to preview the search setup manually, but make sure its selected range and options match the macro’s intended scope. A VBA operation is not reversed by Undo, so a backup is the reliable fallback.

When VBA is not the best option

For a single, occasional change, Excel’s built-in Replace dialog is often simpler than writing a macro. Select the intended cells first, open Home > Find & Select > Replace, enter the search and replacement text, and review the options such as matching case or matching the entire cell. Selecting the range before opening the dialog helps keep the operation scoped, but confirm the selection and dialog settings before choosing Replace All.

If the goal is to create corrected output without changing source data, a worksheet formula or Power Query transformation may be a better fit. A formula can produce a separate result column that is easy to review; Power Query can apply repeatable cleanup steps when importing or reshaping data. Those approaches differ from VBA Replace: they are not simply a macro that edits the original cell contents in place.

VBA is most useful when the same controlled replacement must be repeated, triggered as part of a larger macro, or applied across a specific range according to other logic. If the task is merely to find a value and read it, use Find or a lookup approach rather than replacing data. Choose the least complex method that provides the control and repeatability you need.

Frequently Asked Questions

How do I replace text in only one Excel column with VBA?
Set the target to that column’s data range, such as Worksheets("Data").Range("B2:B500"), and call Replace on that range. Use a row limit that covers the intended records, or calculate the last row if the list changes size.

What is the difference between xlWhole and xlPart?
xlWhole requires the entire cell content to match the search text. xlPart can match a substring within the cell, so it is more flexible but can change unintended text when the searched fragment appears inside a longer value.

Can VBA replace text in formulas?
A replacement operation can affect formula text in the target range, so do not assume it only changes displayed values. Keep formulas outside the target range when possible, and test on a copy before replacing a string that might appear in a formula.

Why does Range.Replace behave differently from the Replace dialog?
Excel remembers some Find and Replace settings, and settings in the dialog can influence later operations if arguments are omitted. Specify options such as LookAt, MatchCase, and format settings in the VBA call rather than depending on prior interface choices.

Can I undo a VBA replacement with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal Undo history, so Ctrl+Z cannot reverse its edits. Save a separate copy before running a replacement, especially when the range is large or contains formulas.

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.