Error Cell Navigator: Find Every #REF!, #N/A, and #VALUE! in Seconds — with One-Click Hyperlinks to Each One

TL;DR: You inherit a 40-tab workpaper with errors scattered across sheets you haven’t even looked at yet. This macro scans every sheet, finds every #REF!, #N/A, #VALUE!, #DIV/0!, #NUM!, #NULL!, and #NAME?, then builds an error report with clickable hyperlinks. One click on any cell address and Excel jumps straight to the problem. Zero setup — just run it.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with formula error cells — this macro is for triaging broken workbooks
• The macro reads from the existing sheets and writes to a new one; your original data is untouched

Limitations:

• Only scans the UsedRange of each sheet — data outside that range is ignored
• Cells with an error value that also have a data validation error (green triangle) will still be caught — the IsError test doesn’t depend on Excel’s error-checking rules
• Very-hidden sheets (xlSheetVeryHidden) are NOT scanned — the macro treats them the same as hidden sheets
• The error report does not tell you which formula caused a downstream error — if cell A1 has #REF! and cell B1 has =A1+100, the report will show both. Start with the #REF! cells first and the dependent errors often resolve

#The Macro

Option Explicit

Sub ErrorCellNavigator()
    ' ── Error Cell Navigator ────────────────────────────
    ' Scans every visible sheet for formula error values
    ' (#REF!, #N/A, #VALUE!, #DIV/0!, #NUM!, #NULL!,
    ' #NAME?). Builds a clickable "Error-Report" sheet
    ' with hyperlinks back to each cell. Zero input
    ' needed — just run it and get a complete triage list.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Error-Report"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim cell As Range, rng As Range
    Dim outRow As Long, errorCount As Long
    Dim cellAddr As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Remove old report if it exists ─────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    ' ── Create report sheet at the end ─────────────────
    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Write headers ──────────────────────────────────
    With wsOut.Range("A1:D1")
        .Value = Array("Sheet", "Cell", "Error Type", "Cell Contents")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With

    ' ── Scan every sheet ───────────────────────────────
    outRow = 2
    errorCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = OUT_SHEET Then GoTo NextSheet
        If ws.Visible <> xlSheetVisible Then GoTo NextSheet

        ' Get the UsedRange (skip if sheet is empty)
        Set rng = Nothing
        On Error Resume Next
        Set rng = ws.UsedRange
        On Error GoTo CleanUp

        If Not rng Is Nothing Then
            For Each cell In rng
                If IsError(cell.Value) Then
                    ' ── Identify error type ────────────
                    Dim errType As String
                    errType = ErrorTypeName(cell.Value)

                    ' ── Sheet name ─────────────────────
                    wsOut.Cells(outRow, 1).Value = ws.Name

                    ' ── Hyperlinked cell address ───────
                    cellAddr = cell.Address(False, False)
                    wsOut.Hyperlinks.Add _
                        Anchor:=wsOut.Cells(outRow, 2), _
                        Address:="", _
                        SubAddress:="'" & ws.Name & "'!" & cellAddr, _
                        TextToDisplay:=cellAddr

                    ' ── Error type + formula/contents ──
                    wsOut.Cells(outRow, 3).Value = errType
                    If cell.HasFormula Then
                        wsOut.Cells(outRow, 4).Value = "'" & cell.Formula
                    Else
                        wsOut.Cells(outRow, 4).Value = cell.Text
                    End If

                    errorCount = errorCount + 1
                    outRow = outRow + 1
                End If
            Next cell
        End If

NextSheet:
    Next ws

    ' ── Format and summarize ───────────────────────────
    If errorCount > 0 Then
        With wsOut
            .Columns("A:D").AutoFit
            .Columns("D").ColumnWidth = 65
            .Rows(1).AutoFilter
        End With
        wsOut.Activate
        wsOut.Range("A1").Select
        ActiveWindow.FreezePanes = True

        MsgBox "Found " & errorCount & " error(s) across this workbook." & _
               vbCrLf & vbCrLf & _
               "See '" & OUT_SHEET & "' sheet for the full list. " & _
               "Click any cell address to jump directly to the error.", _
               vbExclamation, "Errors Found"
    Else
        MsgBox "No errors found — all cells are clean.", _
               vbInformation, "Scan Complete"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: Human-readable error type ──────────────────
Private Function ErrorTypeName(val As Variant) As String
    ' CVErr constants let us distinguish the seven Excel
    ' error types by comparing the cell's value directly.
    If val = CVErr(xlErrRef) Then
        ErrorTypeName = "#REF! — Broken Reference"
    ElseIf val = CVErr(xlErrNA) Then
        ErrorTypeName = "#N/A — Value Not Available"
    ElseIf val = CVErr(xlErrValue) Then
        ErrorTypeName = "#VALUE! — Wrong Data Type"
    ElseIf val = CVErr(xlErrDiv0) Then
        ErrorTypeName = "#DIV/0! — Division by Zero"
    ElseIf val = CVErr(xlErrNum) Then
        ErrorTypeName = "#NUM! — Invalid Numeric Value"
    ElseIf val = CVErr(xlErrNull) Then
        ErrorTypeName = "#NULL! — Invalid Intersection"
    ElseIf val = CVErr(xlErrName) Then
        ErrorTypeName = "#NAME? — Unrecognized Name"
    Else
        ErrorTypeName = "Unknown Error"
    End If
End Function

#How It Works

#CVErr comparisons catch all seven Excel error types

Excel has exactly seven formula error values. Instead of string-matching cell.Text (which might display differently depending on Excel’s language settings), the helper function compares the cell’s value directly against VBA’s built-in CVErr constants:

If val = CVErr(xlErrRef) Then
    ErrorTypeName = "#REF! — Broken Reference"

Each error type gets a human-readable description — not just the abbreviation. #REF! becomes #REF! — Broken Reference; #N/A becomes #N/A — Value Not Available. A preparer who’s never seen a #NULL! error before now knows it means “Invalid Intersection” without Googling it.

This approach also survives Excel language switching. A formula error cell on a German Excel install will still compare equal to CVErr(xlErrRef) because the error value is a numeric code (2023 for #REF!, 2042 for #N/A), not a display string.

#Why hyperlinks matter: one click, no hunting

Each cell address in the report is a hyperlink. Click FA-Detail!D15 and Excel jumps directly to cell D15 on the Fixed Assets Detail sheet. No scrolling, no “which sheet was that again?”, no Ctrl+G dialog. The hyperlink uses SubAddress:="'" & ws.Name & "'!" & cellAddr, which wraps the sheet name in single quotes to handle spaces and special characters automatically.

This is the difference between an error list you read and an error list you use. Every preparer eventually faces the workbook with 40+ errors — the hyperlinks turn a triage nightmare into a checklist you can click through in under a minute.

#The report shows formulas, not just error text

If cell.HasFormula Then
    wsOut.Cells(outRow, 4).Value = "'" & cell.Formula
Else
    wsOut.Cells(outRow, 4).Value = cell.Text
End If

When the error cell contains a formula (which it almost always does — #REF! from a broken VLOOKUP, #N/A from a failed MATCH), the report shows the formula itself, not the error text. Seeing =VLOOKUP(A15,'[Budget.xlsx]FA'!$B:$D,3,FALSE) tells you exactly what broke and how to fix it. The leading apostrophe (') in the cell value prevents Excel from trying to evaluate the formula string when you click on the report cell.

If the error is a plain value (rare — usually from a copy-paste-values of a broken cell), the report shows the error text (#REF!) instead. Either way, you know what you’re looking at.

#UsedRange keeps the scan fast

UsedRange is an Excel built-in property that returns the smallest rectangle containing all cells with data, formatting, or formulas. Instead of scanning every cell on every sheet (1,048,576 rows × 16,384 columns per sheet), the macro only touches cells Excel knows are in use:

Set rng = ws.UsedRange
If Not rng Is Nothing Then
    For Each cell In rng

On a 12-sheet workpaper where each sheet has 200 rows of data, UsedRange checks ~2,400 cells instead of 200 billion. The scan completes in under a second even on a workbook with dozens of tabs.

The On Error Resume Next wrapper is necessary because UsedRange can throw an error on completely empty sheets (no data, no formatting). The macro skips those sheets rather than crashing.

#AutoFilter instead of sorting

Unlike other macros on this blog that sort output by account code or confidence rating, the error report uses an AutoFilter on the header row:

.Rows(1).AutoFilter

This lets the preparer filter by error type (show only #REF! cells to fix broken references first), by sheet name (focus on one workpaper tab at a time), or by any combination. Sorting would lose the natural scan order of sheet-by-sheet. The filter preserves context while giving the user instant filtering.

#Why the report sheet goes at the END

Set wsOut = ThisWorkbook.Worksheets.Add( _
    After:=ThisWorkbook.Worksheets( _
        ThisWorkbook.Worksheets.Count))

The error report is a triage tool, not a permanent fixture. Putting it at the end keeps it out of the way while you flip through the actual workpaper tabs. You fix an error, click back to the report, click the next hyperlink, repeat — with the report as the last tab, it’s always a quick Ctrl+PageUp away.

#Adapt It