Sub to check and report any formula returned error (eg: #REF!)

[Max]

I need a sub to run a quick check through a list of sheets (using their
codenames) filled with a ton of formulae, and pop up an all clear msg if
there are no errors (eg: #REF!) returned in any formula cell. If there are
errors, msg will list the affected codenames. Thanks

 

[Héctor Miguel]

hi, Max !

I need a sub to run a quick check through a list of sheets (using their codenames) filled with a ton of formulae
and pop up an all clear msg if there are no errors (eg: #REF!) returned in any formula cell.
If there are errors, msg will list the affected codenames. Thanks

try with someting like...

Sub ChkErr()
Dim ws As Worksheet, Msg As String
For Each ws In Worksheets
On Error Resume Next
Msg = Msg & vbCr & ws.CodeName & ": " & _
ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Address(0, 0)
Next
MsgBox "Errors found in..." & IIf(Msg <> "", Msg, vbCr & "All Clear !!!")
End Sub

hth,
hector.

 

[Max]

Positively brilliant, Hector. Thanks

How could the sub be tweaked a little to write the results of the checks
into a new sheet? (instead of the msgbox)

 

[Héctor Miguel]

hi, Max !

How could the sub be tweaked a little to write the results of the checks into a new sheet? (instead of the msgbox)

this could be one way...

Sub ChkErr()
Dim ws As Worksheet, Tmp As String, Msg As String, n As Byte, TmpArray
For Each ws In Worksheets
On Error Resume Next
Tmp = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Address(0, 0)
If Err = 0 Then Msg = Msg & ";" & ws.CodeName & ": " & Tmp
Next
If Msg <> "" Then
TmpArray = Split(Mid(Msg, 2), ";")
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count)
[a1] = "Errors found on sheet(s)..."
For n = LBound(TmpArray) To UBound(TmpArray)
[a2].Offset(n).Value = TmpArray(n)
Next
Else
MsgBox "No errors found !"
End If
End Sub

hth,
hector.

 

[Max]

Many thanks Hector, that does it well.

 

[Héctor Miguel]

glad to be in help, Max

and thanks for your feed-back

regards,
hector.