Excel VBA Find

What this does

VBA offers three related tools and they solve different problems. Range.Find locates a value and hands back the cell as a Range object, or Nothing when there is no match — it tells you where something is. Range.Replace changes every match in the target range in a single call and returns only True or False; it is far faster than looping when you do not need to know which cells changed. FindNext continues a search from a previous hit, and the correct way to iterate is to remember the address of the first result and stop when the search wraps back round to it, since Find loops the range endlessly otherwise. Separately, VBA's own Replace() function works on a string rather than a sheet: Replace(text, "a", "b") returns a modified copy and never touches a cell, which is what you want inside a loop over values you have already read. The single biggest trap is that Find's unspecified arguments persist from the last search anyone performed, including one a user did through the dialog — so a macro that omits LookIn behaves differently depending on the session. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “excel vba find” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

A sheet full of records is marked "DRAFT" in mixed positions and casing, and every one should read "FINAL". Sub FindAndReplaceInSheet() first calls Find to confirm at least one match exists and reports its address, then calls Replace once to change them all. The If hit Is Nothing guard is what stops the macro dying on the .Address line when the sheet is already clean. When you need to act on each match rather than just rewrite it — say, colouring every cell containing "draft" — use the FindNext pattern: store firstAddress = hit.Address, do the work, then Set hit = ws.Cells.FindNext(hit) and loop while Not hit Is Nothing And hit.Address <> firstAddress. Without that address comparison the loop never ends, because Find wraps around to the beginning of the range. Find and Replace is where macros meet messy real data — inconsistent labels, stray markers, values that need normalising before anything else can run. The two behaviours worth internalising are that a miss returns Nothing rather than erroring, and that omitted arguments are inherited rather than defaulted; between them they account for most of the bug reports this pair of methods generates. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.

In Google Sheets

If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “excel vba find”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.

Common mistakes

• Using the result of Find without checking for Nothing. Any property access on Nothing raises error 91, and a sheet that simply has no match is the normal case, not an exceptional one.
• Leaving LookIn and LookAt unspecified. Those arguments persist between searches — including from the user's last use of Ctrl + F — so the same macro can match whole cells one day and partial text the next.
• Looping FindNext without tracking the first address. The search wraps around at the end of the range, so the loop runs forever; comparing against the stored first address is the standard termination test.
• Searching with LookIn:=xlValues when you meant formulas. xlValues sees displayed results, xlFormulas sees the underlying text, so a macro hunting for a cell reference must use the latter.
• Confusing Range.Replace with VBA's Replace() function. The first edits cells on a sheet, the second returns a modified string and changes nothing — using the wrong one produces code that appears to do nothing at all.

Frequently asked questions