Prev: Protect/unprotect a list of sheets via their codenames
Next: Speed and the "number dates".
From: Max on 5 Feb 2010 12:25
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
From: H�ctor Miguel on 5 Feb 2010 23:45
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.


From: Max on 6 Feb 2010 16:53
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)


From: H�ctor Miguel on 6 Feb 2010 18:43
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.


From: Max on 6 Feb 2010 22:00
Many thanks Hector, that does it well.


 |  Next  |  Last
Pages: 1 2
Prev: Protect/unprotect a list of sheets via their codenames
Next: Speed and the "number dates".