excelavon

VBA Find: How to Handle a Failed Search

VBA & macros · Updated November 3, 2026

VBA Find: How to Handle a Failed Search

In Excel VBA, handle a failed Range.Find search by checking whether its result is Nothing before using it. Finding no match is a normal outcome, not an error, so test for it explicitly; use an error handler separately for problems such as a missing worksheet or an invalid range.

What “Find failure” means in Excel VBA

People asking how to “handle VBA Find failure” are usually asking what to do when Excel’s Find method cannot locate a requested value. The method returns a Range object when it finds a match and returns Nothing when there is no match in the specified search range. The key is to store the result in an object variable and check it before referring to its address, value, row, or other properties.

This is different from a VBA runtime error. A search for an absent invoice number can complete successfully and return Nothing; by contrast, trying to use a worksheet that does not exist can raise an error. Treating every no-match result as an exception can make a macro harder to read and can hide genuine problems behind a broad error handler.

If “handle failure” means a Windows API handle rather than Excel’s Find method, that is a separate topic: API code must check the handle value returned by the relevant Windows function. The examples here cover Excel’s built-in Range.Find method, which is the usual meaning in spreadsheet VBA.

Use a Nothing check before accessing the result

This example searches a defined area on a worksheet for an exact invoice identifier. It reports a match or a clear no-match message, and it has a separate handler for unexpected runtime errors. Before running any macro that might change a workbook, save a copy: VBA macros cannot be undone with Ctrl+Z.

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

    On Error GoTo ErrHandler

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")
    searchText = "Invoice-1042"

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

    If foundCell Is Nothing Then
        MsgBox "No match for " & searchText & " in " & searchRange.Address
    Else
        MsgBox "Found at " & foundCell.Address(False, False)
    End If
    Exit Sub

ErrHandler:
    MsgBox "The search could not run: " & Err.Description
End Sub

The example assumes a worksheet named Data and searches A2:A100. Change both to match the workbook. Setting After to the last cell makes the search begin at the start of the range after Excel wraps around. A match is returned as a cell object; the code only reads its address after confirming that the object is not Nothing.

Specify the search settings every time

Excel’s Find dialog and VBA searches share several search settings. Some options can persist from a previous search, including one a user performed manually in the Find dialog. If a macro omits arguments, it may therefore behave differently depending on earlier activity. For dependable results, state the important arguments in every call instead of relying on defaults.

  • What is the value or text to look for.
  • LookIn selects what Excel searches, such as displayed values with xlValues or formulas with xlFormulas.
  • LookAt uses xlWhole for a complete-cell match or xlPart for a match within the cell.
  • SearchOrder chooses row-by-row or column-by-column order; xlByRows is common for a vertical list.
  • SearchDirection selects xlNext or xlPrevious.
  • MatchCase controls whether letter case matters. SearchFormat controls whether formatting criteria are included.

For ordinary text lookup, LookIn:=xlValues, LookAt:=xlWhole, and SearchFormat:=False are often a sensible starting point. Choose settings based on the task, though. Searching values may differ from searching formulas: a cell displaying 120 could contain a literal number or a formula that calculates 120. If you need to find the formula text itself, use xlFormulas.

Find every match without looping forever

Find returns only one matching cell. To process all matches, use FindNext and stop when the search wraps back to the first address. Excel’s search wraps around at the end of a range, so a loop that keeps calling FindNext without recognizing that return point can run indefinitely.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")

    Set foundCell = searchRange.Find( _
        What:="Invoice-1042", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No matching invoices were found."
        Exit Sub
    End If

    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
    Loop While foundCell.Address <> firstAddress
End Sub

This version prints each matching cell address and value in the Immediate window, which you can show in the VBA editor with Ctrl+G on Windows. The loop checks for Nothing before asking for the next address. That separate check matters because VBA does not short-circuit the two sides of an And expression; a condition that combines “not Nothing” with foundCell.Address may still try to read the address when the object is Nothing.

Distinguish no match from a real runtime error

A useful macro handles expected outcomes directly and reserves an error handler for exceptional failures. If foundCell Is Nothing Then is the ordinary branch for a search with no result. An On Error GoTo handler is appropriate for issues such as a misspelled worksheet name, a deleted sheet, or another unexpected problem while setting up or running the search.

A broad On Error Resume Next around the entire macro is usually a poor substitute. It can suppress the very error that explains why the search did not run, leaving the user with a misleading no-match message or a later failure. If you use On Error Resume Next for a narrowly defined operation, check Err.Number immediately afterward and restore normal handling with On Error GoTo 0. For most Find macros, an explicit Nothing check plus a targeted error handler is easier to maintain.

Also verify the object references before searching. ThisWorkbook refers to the workbook containing the macro; ActiveWorkbook refers to whichever workbook is active. If the macro is stored in Personal.xlsb or an add-in, those references can point to different workbooks. Use the reference that matches where the data actually resides.

Common reasons a search appears to fail

A correct Nothing check will tell you that no match was found, but it cannot correct a mismatched search setup. Before changing the code, compare the target, range, and Find options with the worksheet data.

  • The range excludes the cell. A search of A2:A100 will not find a value in A101 or on another worksheet. Confirm the range’s address and its parent worksheet.
  • The match setting is too strict. xlWhole will not match the text “1042” inside “Invoice-1042”; use xlPart if a partial match is intended.
  • The cell contains a formula. Decide whether to search its calculated value or the formula itself, then choose xlValues or xlFormulas accordingly.
  • Formatting is influencing the search. Set SearchFormat:=False for a value search that should ignore formatting. If intentionally searching by format, set the format criteria deliberately.
  • Text is not identical to what it looks like. Leading or trailing spaces, nonbreaking spaces, punctuation, and numeric-versus-text storage can make visually similar entries differ. Inspect the underlying cell contents or test a cleaned copy of the data.
  • Wildcards are being interpreted. In a text search, an asterisk and question mark can act as wildcard characters. If you need to search for a literal wildcard character, escape it with a tilde, such as ~* for an asterisk.

When the result is surprising, temporarily print the search range address and the exact search text with Debug.Print. This can reveal that the macro is searching a different sheet or using a value with an unnoticed space. Avoid changing several Find arguments at once; test one likely cause at a time.

When Find is the right tool, and when it is not

Range.Find is useful when you need to locate a matching cell and then inspect or modify that cell. It can search a limited range, match whole or partial contents, and search in a chosen direction. For a one-off lookup in a short list, it is often more direct than writing a loop that checks every cell individually.

For a calculation that needs to return a corresponding value, a worksheet lookup formula may be a better fit. In Microsoft 365, XLOOKUP can return a chosen result when a match exists and can specify a value for the not-found case. VBA Find is more suitable when the macro needs the actual cell object, such as selecting a record, formatting a match, or processing multiple matching cells. For larger data tasks, consider whether a table, filter, or a purpose-built data process would be clearer than repeated cell-by-cell searches.

Keep the search range as specific as practical. Searching an entire worksheet can make behavior harder to reason about and may be slower than searching the relevant column or table. If the data grows over time, a table’s data range can be a more maintainable target than a fixed address, provided the code is written to refer to that table correctly.

Frequently Asked Questions

Does VBA Find raise an error when there is no match?
No. Excel normally returns Nothing when the search finds no matching cell. Check the returned object before using it; use an error handler for separate runtime problems.

Why does Range.Find return Nothing even though I can see the value?
Check that the right worksheet and range are being searched, then verify LookIn, LookAt, case sensitivity, formatting criteria, and hidden spaces. A displayed value may come from a formula, so the choice between searching values and formulas can also matter.

What is the safest way to search for an exact match?
Set LookAt:=xlWhole, specify the intended LookIn value, set SearchFormat:=False if formatting should not matter, and test the result with If foundCell Is Nothing Then.

How do I make VBA Find search all matches?
Call Find once, save the first match’s address, and use FindNext until it returns to that address. Also check whether each result is Nothing before accessing it, because Find searches wrap around the range.

Is Find failure the same as an invalid Windows handle?
No. Excel’s Range.Find returns a cell reference or Nothing. Windows API handle failures involve operating-system functions and different return values; they require checking the specific API’s documented result rather than using Excel’s Find pattern.

Related

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.