Excel VBA: Find a Cell with Specific Text in a Range

To find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as LookAt:=xlWhole for an exact match or LookAt:=xlPart for text contained within a cell, and check whether the result is Nothing before using it.

Find text in a specified range

This example searches cells A2:D100 on a worksheet named Data and reports the first cell containing the phrase “late.” It searches cell values, allows the phrase to appear within a longer entry, and ignores letter case.

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

    ' Set the worksheet, cells to search, and text to find.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:D100")
    searchText = "late"

    Set foundCell = searchRange.Find( _
        What:=searchText, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    If foundCell Is Nothing Then
        MsgBox "Text not found."
    Else
        MsgBox "Found in " & foundCell.Address(False, False)
    End If
End Sub

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. Replace Data, A2:D100, and late with the worksheet, range, and text relevant to your file. If the sheet name is misspelled or does not exist in the workbook containing the macro, VBA raises an error rather than searching another sheet.

To run it in desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and run the procedure with F5 while the cursor is inside it. The Developer tab also opens Visual Basic. On a Mac, use Developer > Visual Basic or the keyboard shortcut Option+F11; some keyboards require Fn as well. Excel for the web does not run VBA macros, so open the workbook in desktop Excel.

What Range.Find does

Find searches a Range object and returns a Range object for the first matching cell in its search sequence. It does not return the cell’s text directly. That is why the example stores the result in foundCell, tests it with If foundCell Is Nothing, and then reads its address. You can also inspect properties such as foundCell.Value or foundCell.Formula.

Search results depend on the range you call the method on. Calling searchRange.Find confines the search to that range; calling Cells.Find searches the worksheet’s cells and can take longer than necessary. Define a limited range when the relevant data area is known. If you need to search a whole column, a reference such as ws.Columns("A") is possible, but it includes many blank cells and may be less efficient than a used data range.

The method can search values or formulas, match entire cell contents or a substring, and move through cells by row or column. It returns the first match according to those settings, not necessarily the topmost cell unless the order and starting position produce that result.

Choose exact or partial matching

The LookAt argument controls whether the entire cell must match. Choose xlWhole when searching for a cell whose complete content is the target, such as finding the status “Closed” without also matching “Closed – pending review.” Choose xlPart when the target may be embedded in a longer value, such as finding “closed” in that longer status.

MatchCase:=False makes the search case-insensitive; set it to True when capitalization matters. For example, a case-insensitive search for “acme” can match “ACME” or “Acme.” This option concerns letter case, not spelling, extra spaces, or punctuation. If a cell contains trailing spaces, an exact search may not behave as expected because the cell’s actual contents include those spaces.

Other arguments worth setting explicitly are:

LookIn:=xlValues searches cell values, including the calculated results shown by formulas.
LookIn:=xlFormulas searches formula text. This is useful when looking for a function or reference written inside formulas rather than the displayed results.
SearchOrder:=xlByRows moves through rows; xlByColumns moves through columns.
SearchDirection:=xlNext searches forward through the order; xlPrevious searches in reverse.

For instance, a cell with a formula that evaluates to “Ready” can be found by searching values for Ready. Searching formulas instead looks for text in the formula itself; it will not find that cell merely because its displayed result is Ready.

Find every matching cell

A single Find call returns one result. To process all matches, remember the first match’s address, then use FindNext until the search wraps back to that address. This example prints each matching address and value to the Visual Basic Editor’s Immediate window. Open that window with Ctrl+G in the editor.

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

    ' Define the cells and text to search.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:D100")
    searchText = "late"

    Set foundCell = searchRange.Find( _
        What:=searchText, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

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

The explicit Nothing check inside the loop matters: VBA’s And operator does not short-circuit, so combining “not Nothing” and an address comparison in one condition can still attempt to read the address of a missing result. FindNext continues the existing search, so its behavior relies on the settings supplied to the initial Find call. The address check stops the loop when Excel cycles back to the first result.

Set the search range and starting point carefully

The After argument specifies the cell after which the search begins. It must be one cell within the range being searched. In the examples, the last cell is supplied, making the first search step begin at the start of the range in the selected direction. Excel can wrap around to the beginning or end of the range, so the first result is not always the first cell in worksheet order unless you account for the chosen start and direction.

Use a clearly defined worksheet variable rather than relying on ActiveSheet or Selection. Those objects change when a user clicks another sheet or selects a different range, which can make a macro search the wrong place. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel. Choose deliberately if the code is stored in Personal.xlsb or an add-in but should search a separate workbook.

For a table that grows over time, a fixed reference such as A2:D100 may eventually omit new rows. You can update the range to match the data layout, or search a table’s data body range. Avoid assuming that the worksheet’s used range always represents a clean data block: formatting left far below the data can make that range larger than expected.

Common problems and their fixes

The macro reports no match: Confirm the range includes the cell, check for extra spaces or different punctuation, and verify whether the search should use xlWhole or xlPart. Also confirm that you are searching values rather than formula text, or vice versa.
It finds a surprising result: Check whether partial matching is enabled and whether letter case matters. A search for a short word can match a larger word when xlPart is used.
Results change between runs: Excel’s Find settings are shared with the Find dialog and can persist from earlier searches. Supplying relevant arguments in VBA makes the macro’s intended behavior clearer and more dependable. Formatting search settings can also affect results if formatting searches have been used; avoid relying on an unspecified search format.
The code raises an object error: A missing worksheet name, an invalid range reference, or trying to use a result before checking for Nothing are common causes. Ensure the searched range is valid and that every possible no-match result is handled.
A wildcard changes what matches: In Find, * represents any sequence of characters and ? represents one character. Use ~* or ~? to search for those characters literally; use ~~ for a literal tilde.

Find is a search operation, not a cleanup tool. It does not trim spaces, normalize inconsistent text, or determine whether two differently formatted values mean the same thing. If the data is inconsistent, standardize it or add a separate comparison step rather than assuming the search method will interpret it.

When to use Find instead of other methods

Use Range.Find when you need a VBA macro to locate a cell, especially when the range is large or you want Excel’s built-in search options. Use a direct comparison in a loop when the matching rule is more specialized—for example, when the code must test several columns together, ignore selected punctuation, or apply a custom rule to each row. A loop offers control but may require more code and can be slower if it reads cells one at a time across a very large range.

For a one-time manual search, Excel’s Find dialog is often simpler: press Ctrl+F on Windows, enter the text, and use Find Next or Find All. On Mac, use Command+F. The dialog offers search options such as matching case and matching entire cell contents. VBA is more useful when the search must be repeated, tied to a button, or followed by automated processing. Excel for the web supports manual workbook searching but not VBA macro execution.

Frequently Asked Questions

How do I find an exact cell value in VBA?
Use Range.Find with LookAt:=xlWhole. Specify MatchCase:=False or True according to whether capitalization should matter, and check for Nothing before using the returned range.

How do I find text anywhere inside a cell?
Set LookAt:=xlPart. A search for “late,” for example, can then match a cell containing “delayed.” Use a longer search phrase if short text would produce unwanted matches.

Can VBA search a formula’s result?
Yes. Use LookIn:=xlValues to search evaluated values, including formula results. Use LookIn:=xlFormulas when the target is text in the formula expression itself.

How do I search the whole worksheet?
Call Find on the worksheet’s Cells range, such as ws.Cells.Find, and provide explicit search options. A bounded range is usually preferable when you know where the data is, because it avoids searching irrelevant cells.

Why does Find return Nothing?
It means no cell in the specified range matched under the current options. Check the range, exact-versus-partial setting, case setting, search target (values or formulas), and whether the text includes spaces or wildcard characters.

Can I run this macro in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in the desktop version of Excel to use the Visual Basic Editor and execute the macros.

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.