🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Find in Excel — Search Cells the Right Way (It Returns a Range, Not a Position)

|

VBA Find in Excel — Search Cells the Right Way (It Returns a Range, Not a Position)

TL;DR — Range.Find is Excel's Ctrl+F for code. It does not return a row number or a position — it returns a Range pointing at the matching cell, or Nothing if there is no match. So the very first thing you do with the result is check Is Nothing; skip that and the first no-match run crashes with "Object variable not set" (error 91). Two arguments are non-negotiable because Find remembers them from last time: set LookIn:=xlValues and LookAt:=xlWhole on every call, or your macro inherits whatever a user last typed into the Find dialog.

' Find "Widget" in column A and report its row - safely.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
Dim found As Range
Set found = ws.Columns("A").Find( _
        What:="Widget", _
        LookIn:=xlValues, _
        LookAt:=xlWhole)          ' xlWhole = cell EQUALS "Widget", not just contains it

If found Is Nothing Then
    MsgBox "Not found"
Else
    MsgBox "Found in row " & found.Row     ' found is a Range - use .Row, .Address, .Value
End If

Searching a sheet is the single most common thing a macro does, and Find is the right tool for it — it is Excel's own optimized search engine, and it beats a hand-written loop over every cell by a wide margin on real data. But Find has more hidden state than almost any method in the object model, and that state is why the same code gives different answers on different machines. This guide is built around two facts — Find returns an object, and Find is sticky — and everything else follows from them.

What you'll learn

  • The mental model — Find returns a Range (or Nothing), not a position
  • The check that prevents the error-91 crash — test Is Nothing before you touch the result
  • The sticky-arguments trap — why LookIn and LookAt must be set every single time
  • xlWhole vs xlPart — the difference between "equals" and "contains"
  • Looping with FindNext to get every match, without an infinite loop
  • When Find beats a loop, and how it differs from InStr

The mental model: Find returns a cell, not a number

If you come from InStr, Find looks familiar and behaves nothing like it. InStr searches inside a string and hands back a number — the character position of the match, or 0. Range.Find searches across cells and hands back a Range — a live pointer to the cell it landed on, or the special value Nothing when nothing matched.

That single difference drives everything. Because the result is an object, you assign it with Set, and you read what you need off it: found.Row, found.Column, found.Address, found.Value. And because "no match" is represented by Nothing rather than 0 or "", you cannot test it with =. If found = Nothing will not even compile the way you expect — Nothing is checked with the Is keyword: If found Is Nothing.

Hold that picture — Find gives you the cell or it gives you Nothing — and the number-one crash below is obvious before it happens.

The rule that matters most: check Is Nothing before you touch the result

Here is the bug, and almost everyone writes it once:

' WRONG - assumes Find always succeeds.
Dim found As Range
Set found = ws.Columns("A").Find(What:="Widget", LookIn:=xlValues, LookAt:=xlWhole)
MsgBox found.Row        ' error 91 the moment "Widget" isn't there

The code works perfectly in testing — because your test data contains "Widget." Ship it, feed it a sheet where the value is missing, and Find returns Nothing. The next line asks Nothing for its .Row, and VBA raises run-time error 91, "Object variable or With block variable not set." It is the most common Find error there is, and it is entirely preventable:

' RIGHT - branch on Is Nothing first.
Set found = ws.Columns("A").Find(What:="Widget", LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
    ' handle "not found" - message, default, exit
Else
    ' safe to use found.Row, found.Value, etc.
End If

Every Find is followed by an Is Nothing test. Treat the two as a single unit you type together, the same way On Error and its handler travel together. There is no such thing as a Find you are sure will match — the whole point of searching is that you do not know.

The trap that wastes hours: Find remembers its last arguments

This is the behaviour that makes Find feel haunted. Most of its arguments are optional, and when you omit one, Find does not use a fixed default — it reuses the value from the last time Find ran, anywhere in this Excel session. That includes the last search a user typed into the Ctrl+F dialog by hand.

So this innocent-looking call:

Set found = ws.Cells.Find(What:="2026")     ' LookIn? LookAt? MatchCase? ...inherited

can match on formulas one run and values the next, match whole-cell one run and partial the next — depending on state you cannot see and did not set. The symptom is maddening: "it worked yesterday," "it works on my machine," "it finds it sometimes." The fix is discipline — pass the arguments that decide the result explicitly, every time:

Set found = ws.Cells.Find( _
        What:="2026", _
        LookIn:=xlValues, _      ' xlValues (what you see) vs xlFormulas (the underlying formula)
        LookAt:=xlWhole, _       ' xlWhole (equals) vs xlPart (contains)
        MatchCase:=False, _
        SearchOrder:=xlByRows)

My rule: if an argument could change which cell you land on — LookIn, LookAt, MatchCase — you name it on every call and never rely on the default. The three keystrokes are cheaper than the bug report.

xlWhole vs xlPart: equals versus contains

LookAt is the argument people get wrong most often, because both values "work" and only one is correct for the job. LookAt:=xlWhole matches a cell whose entire contents equal your search term. LookAt:=xlPart matches any cell that contains the term anywhere inside it.

' Cell contains the text: Order 100 shipped
ws.Cells.Find(What:="100", LookAt:=xlWhole)   ' NO match - cell isn't exactly "100"
ws.Cells.Find(What:="100", LookAt:=xlPart)    ' matches - "100" appears inside the text

Looking up an ID, a code, an exact key? You want xlWhole, or you will match "100" inside "1002", "31007", and "Order 100." Scanning for a keyword inside longer text? You want xlPart. Choosing the wrong one is the classic "why did it match that cell?" bug, and it never raises an error — it just returns the wrong Range.

Finding every match: the FindNext loop

Find returns one cell — the first match after the cell you tell it to start from. To act on every occurrence, you loop with FindNext, which continues from the previous hit. The catch: FindNext wraps around to the top of the range when it runs off the end, so a naive loop runs forever. The fix is to remember the address of the first match and stop when you circle back to it:

Dim found As Range, firstAddress As String
Set found = ws.Columns("A").Find(What:="Widget", LookIn:=xlValues, LookAt:=xlWhole)

If Not found Is Nothing Then
    firstAddress = found.Address          ' <-- remember where we started
    Do
        found.Offset(0, 1).Value = "seen" ' do something with each match
        Set found = ws.Columns("A").FindNext(found)
    Loop While Not found Is Nothing And found.Address <> firstAddress
End If

Forgetting to capture firstAddress is the number-one FindNext bug — the loop laps the range endlessly, re-processing the same matches. One more rule that quietly matters: do not delete or insert rows inside a FindNext loop. Changing the grid mid-search invalidates Find's sense of position; if you need to delete matches, collect their addresses first and delete afterward (see VBA Delete Rows for the backwards-loop and Union patterns).

Find and Replace in code

Range.Replace is Find's sibling and shares the same sticky arguments, but it does the whole job in one call — no loop, no Is Nothing:

ws.Columns("A").Replace _
        What:="N/A", Replacement:="0", _
        LookAt:=xlWhole, MatchCase:=False

Because Replace acts on the entire range at once, it is far faster than looping Find + writing each cell — and it returns True/False for whether anything changed rather than a range. Reach for Replace when you want to change matches and for Find/FindNext when you want to inspect or navigate to them.

They sound interchangeable and are not. Use Find when the thing you are searching is the worksheet — you want the cell, and you want Excel's fast native search. Use InStr when the thing you are searching is a single string already in a variable — you want a character position inside that text. A common pattern uses both: Find locates the row, then InStr picks a substring out of one field on that row. If you are doing text surgery inside strings, that is InStr and friends, not Find.

And the judgment call worth stating plainly: do not write a For Each cell In range loop to find a value. Find is Excel's own indexed search; on a column of 100,000 rows it returns almost instantly while the loop grinds through every cell. Hand-rolling the search is slower, longer, and easier to get wrong.

How ExcelMaster helps

Find concentrates an unusual amount of hidden behaviour into one method: the object-not-number return, the Nothing result, the sticky arguments, the xlWhole/xlPart choice, the wrap-around loop. Each has a failure mode that produces wrong results without raising an error — exactly the kind of bug that survives testing and surfaces in production.

ExcelMaster lets you describe the search instead. Say "find every row where the status is Cancelled and flag it" or "look up this invoice number and jump to it," and it writes the Find with the arguments pinned explicitly, the Is Nothing guard in place, and a proper FindNext loop when you need all matches — backing up the sheet before it changes anything. You keep the workbook and the code; you skip the part where an inherited LookAt quietly matches the wrong cell.

Frequently asked questions

Why does VBA Find give error 91?

Because Range.Find returns Nothing when there is no match, and error 91 ("Object variable or With block variable not set") fires the instant you read a property like .Row off Nothing. Always test If found Is Nothing Then before using the result. The crash usually appears only when your data lacks the search term, which is why it slips past testing.

What does VBA Find return if there is no match?

It returns the special value Nothing, not 0 and not an empty string. You must check it with the Is keyword — If found Is Nothing — because Nothing cannot be compared with =. On a successful match it returns a Range pointing at the found cell.

What is the difference between LookAt xlWhole and xlPart in VBA Find?

xlWhole matches only cells whose entire contents equal the search term; xlPart matches any cell that contains the term anywhere inside it. Use xlWhole for exact keys like IDs and codes, and xlPart when scanning for a keyword inside longer text. Picking the wrong one returns the wrong cell without any error.

How do I find all matching cells in VBA, not just the first?

Use Find to get the first match, remember its .Address, then loop with FindNext(found) until the address comes back around to the first one: Loop While Not found Is Nothing And found.Address <> firstAddress. Capturing the first address is essential — without it the loop wraps around the range forever.

Should I use VBA Find or a loop to search a sheet?

Use Find. It is Excel's built-in optimized search and is far faster than a For Each loop over every cell, especially on large ranges. A manual loop is also longer and more error-prone. Only loop when you need custom match logic that Find's arguments cannot express.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-08.

Related guides: VBA AutoFilter · VBA Sort · VBA Delete Rows · VBA InStr · VBA Range