Excel VBA Find Text in a Range: Code and Examples Explained

To find text in an Excel range with VBA, use the range’s Find method and check whether it returns a cell. For example, search A1:D100 for a partial, case-insensitive match with LookAt:=xlPart and MatchCase:=False; use xlWhole when the whole cell must match. The example below also shows how to find every match.

What the Find method does

Range.Find searches the cells in a specified range and returns a Range object for the first matching cell. If it finds nothing, it returns Nothing. This makes it useful when a macro needs to report a match, select a cell, read its value, or change its formatting.

The method searches in a chosen direction and can look for a complete cell value or a text fragment. For example, searching for North with a partial match can find North Region, while an exact match will not. The search can also distinguish uppercase and lowercase letters if you ask it to.

A key difference from Excel’s Find dialog is that VBA can define the search range precisely and can repeat the search programmatically. However, the method shares some search settings with Excel’s Find interface. Specify the important arguments in code rather than relying on settings left over from a previous search.

A macro to find text and list every match

This Windows desktop example searches cells A1:D100 on a worksheet named Sheet1 for cells containing the text “north.” It prints each matching cell address in the Visual Basic Editor’s Immediate window. Change the worksheet name, range, and search text to suit your workbook.

Option Explicit

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

    ' Set the worksheet and range to search.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A1:D100")
    searchText = "north"

    ' Search cell values for a partial, case-insensitive match.
    Set foundCell = searchRange.Find( _
        What:=searchText, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        Debug.Print "No match found."
    Else
        firstAddress = foundCell.Address
        Do
            Debug.Print foundCell.Address
            Set foundCell = searchRange.FindNext(After:=foundCell)
            If foundCell Is Nothing Then Exit Do
        Loop While foundCell.Address <> firstAddress
    End If
End Sub

The After argument is set to the range’s last cell so the search starts at the beginning of the range. Excel searches after the cell supplied in After, then wraps around. The loop records the first match’s address and stops when the search returns to it, preventing an endless loop.

To run the macro, open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. On Windows, Alt+F11 opens the editor; Ctrl+G displays the Immediate window where the addresses appear. On Mac, use Tools > Macro > Visual Basic Editor, or Option+F11 (sometimes Fn+Option+F11, depending on the keyboard). The VBA editor and Immediate window are desktop features, not features of Excel for the web.

Choose exact, partial, and case-sensitive searches

Three arguments control common matching choices. Set LookAt to xlWhole to require the cell’s contents to match the search text, or use xlPart to find the text anywhere within a cell. Set MatchCase to True for case-sensitive matching; False treats uppercase and lowercase letters as equivalent.

For instance, with xlWhole, a search for pear will not match a cell containing ripe pear. With xlPart, it will. With case-sensitive matching, North and north are different strings. These settings are independent: a search can be partial and case-sensitive, or exact and case-insensitive.

Use xlWhole when searching for an ID, code, or complete label.
Use xlPart when searching descriptions or text that may include extra words.
Use MatchCase:=True only when capitalization is meaningful in the data.

Excel also treats the asterisk and question mark as wildcard characters in Find searches. An asterisk represents a sequence of characters, and a question mark represents one character. To search for a literal wildcard character, prefix it with a tilde. This can matter when cell contents include symbols such as product codes with a real asterisk.

Search values or formula contents

The LookIn argument determines what Excel searches. xlValues searches the cell’s value, including the calculated result of a formula. xlFormulas searches formula contents, so it can locate text written in a formula itself. A cell containing ="North Region", for example, has a displayed result and a formula expression; those are different things to search.

Choose the option based on the question the macro needs to answer. If you want to find a result shown in a report, search values. If you need to identify formulas that refer to a particular word or text, search formulas. Neither setting is a general search of every possible representation of a cell, such as its formatting or comment text.

For a visible number, Excel may store a numeric value while displaying it with a number format. Find is not a substitute for inspecting the formatted appearance of a cell. If a search depends on exactly how a value is displayed, first decide whether the underlying value, formula, or displayed text is the intended target.

Control search order and direction

SearchOrder sets the order in which cells are examined. Use xlByRows to search across a row and then move to the next row, or xlByColumns to search down a column before moving to the next one. SearchDirection:=xlNext moves forward in that order; xlPrevious searches in reverse.

These options affect which match is returned first when there are several matches. They do not change which cells qualify. If the code only needs to know whether any match exists, the order may not matter. If it should return the topmost, leftmost, or last matching cell, set the order and direction deliberately and test the result on a small example.

Define the range carefully

The range passed to Find is the boundary of the search. Searching A1:D100 does not search the rest of the worksheet. A fixed range is straightforward for a stable layout, but it can miss new records added below the last row. For a growing list, determine the relevant last row or use an Excel table’s data range so the search covers the data that actually exists.

Also decide whether the search should include a header row, totals, or other worksheet content. A match in a heading may be valid for a broad text search but irrelevant when looking up a record. A smaller, clearly defined range reduces unintended matches and often makes the macro easier to maintain.

Hidden rows are not automatically excluded just because they are hidden. If the requirement is to search only visible cells, that is a separate condition the macro must handle. In a merged area, the value is generally stored in the upper-left cell of the merged range, so searching or reporting a merged-cell result can be surprising if you expect every cell in the area to behave independently.

Find one match or use another method?

Use Find when you need to locate a cell and then work with that cell. It returns a range, so code can read the address, inspect neighboring cells, or apply formatting. Use FindNext when you need to process multiple matches without scanning every cell yourself.

For a small, simple range, a loop through each cell can be easier to customize—for example, when matching must follow several special rules. But a loop needs explicit logic for blanks, errors, and data types, and may be less convenient for ordinary text searches. Excel worksheet lookup functions answer a different kind of question: they return a value or position in a formula rather than giving VBA a cell object to manipulate.

Excel’s Find dialog is a useful manual alternative when a person is searching interactively. VBA is a better fit when the task must be repeated, incorporated into a report, or followed by an automated action. Neither approach changes the underlying distinction between exact and partial matching.

Common problems and how to avoid them

No match appears: Confirm the range, spelling, and LookIn setting. Check whether the text is part of a formula result or only part of the formula.
The wrong cell is returned: Set LookAt, SearchOrder, and SearchDirection explicitly. A partial search may match an unintended longer phrase.
The macro errors after a failed search: Test If foundCell Is Nothing Then before reading its address or value. A failed search has no cell object to inspect.
Results change after using Find in Excel: Supply the search arguments in VBA. Find settings such as match type and search scope can persist between searches.
Only one result is processed: Find returns the first match only. Use FindNext and stop after the first address repeats to process all matches safely.

Setting SearchFormat:=False is especially useful when the macro is meant to match text rather than a previously configured cell format. If you intentionally want to search by formatting, that requires a deliberate format-based search setup; do not rely on an old Find dialog setting.

Run and save VBA safely

VBA macros run in desktop Excel, not Excel for the web. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code. A workbook saved as a macro-free format may not preserve its VBA project.

Before running a macro that changes cells, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, even if the macro performs a change that would normally be reversible by a user. The sample above only prints addresses, but a macro that deletes, overwrites, or formats data deserves a backup and a test on a copy first. Only enable macros in files you trust.

Frequently Asked Questions

How do I find an exact text match in VBA?
Use the range’s Find method with LookAt:=xlWhole. Check whether the returned range is Nothing before using it. Set MatchCase:=True as well if capitalization must match.

How do I find part of a cell’s text?
Set LookAt:=xlPart. For example, searching for blue can find a cell containing light blue shirt. The selected range still determines which cells Excel checks.

How can I find all occurrences instead of just one?
Call Find for the first match, then use FindNext to continue. Save the first match’s address and stop when that address appears again, because the search wraps around to the start of the range.

What does Find return when the text is missing?
It returns Nothing. Test for that result before asking for a cell’s address, value, or row number; otherwise, the macro can raise an error.

Can I run an Excel VBA search in a browser?
No. Excel for the web can open a macro-enabled workbook but cannot run its VBA macros. Use desktop Excel for Windows or Mac to run the macro; the VBA editor and available keyboard shortcuts differ slightly by platform.

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.