Are there any ways to find out/ identify error cells in the entire workbook?

[JoeyCheung]

Hi Everyone,

I know that for each individual worksheet, I can use "Go to special" -> "Formulas" -> "errors" to highlight all of the errors cell in that particular worksheet.

Wondering are there any way to find out all of the Cells that containing formulas errors in the entire Workbook; instead of having to use the "Go to special" function in each single worksheet to find out those errors cells sheet by sheet?

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

You can find Cells with Formulas returning Errors with VBA using the Range.SpecialCells property.

wks.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

If you'd like some help using VBA to find error cells, please clarify what your desired result would be (a list of cells with errors, change the color of those cells?)

Non VBA approaches include...
Conditional Formatting: Make a rule =AND(ISERROR(A1),ISFORMULA(A1)) then apply to all cells on each sheet.

FindAll: A quick and dirty approach if you don't have many values that start with "#"- Try using Excel's Find feature to find formula errors:

Ctrl-F > Find
Find what: #*
Within: Workbook
Look in: Values
Check "Match entire cells contents"
Find All

You can sort any returned values by the Value field to separate out any cells found that are not errors:
(#N/A, #REF!, #NAME?, #DIV/0!, #VALUE!, #NULL!).
This will also return cells displaying "############" which are not considered formula errors by GoTo Special.

 

[JoeyCheung]

Hi and Welcome to MrExcel,

You can find Cells with Formulas returning Errors with VBA using the Range.SpecialCells property.

wks.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

If you'd like some help using VBA to find error cells, please clarify what your desired result would be (a list of cells with errors, change the color of those cells?)

Non VBA approaches include...
Conditional Formatting: Make a rule =AND(ISERROR(A1),ISFORMULA(A1)) then apply to all cells on each sheet.

FindAll: A quick and dirty approach if you don't have many values that start with "#"- Try using Excel's Find feature to find formula errors:

Ctrl-F > Find
Find what: #*
Within: Workbook
Look in: Values
Check "Match entire cells contents"
Find All

You can sort any returned values by the Value field to separate out any cells found that are not errors:
(#N/A, #REF!, #NAME?, #DIV/0!, #VALUE!, #NULL!).
This will also return cells displaying "############" which are not considered formula errors by GoTo Special.

Thanks for your reply!

Is it possible to generate a list of error cells in a separate worksheet, showing the sheet names and cell references for each error in that workbook?

Thanks in advance!

 

[Peter_SSs]

See if this is any use.

Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
  
  Sheets.Add Before:=Sheets(1)
  nr = 1
  For i = 2 To Sheets.Count
    Set rErrors = Nothing
    On Error Resume Next
    Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
    On Error GoTo 0
    If Not rErrors Is Nothing Then
      sName = Sheets(i).Name
      For Each r In rErrors
        nr = nr + 1
        With Sheets(1)
          .Cells(nr, 1).Value = sName
          .Cells(nr, 2).Value = r.Address(0, 0)
          .Cells(nr, 3).Value = r.Text
        End With
      Next r
    End If
  Next i
  Sheets(1).Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub