Code to check errors in excel 2002

[Redan]

Hello,

Does anyone have a vba code that checks if all the formulas inside a
worksheet does not contain any errors such as #REF, etc ... ?

Thank you in advance !

 

[Joel]

MyError = WorksheetFunction.IsError(Range("A1"))

 

[dbKemp]

Hello,

Does anyone have a vba code that checks if all the formulas inside a
worksheet does not contain any errors such as #REF, etc ... ?

Thank you in advance !

Public Function WorksheetErrors(ByRef Target As Range) As Boolean
Dim rCell As Range
Dim vErrorArray As Variant
Dim iCounter As Integer

vErrorArray = Array("#N/A", "#DIV/0!", "#NAME?", "#NULL!", "#NUM!",
"#REF!", "#VALUE!")
Application.EnableEvents = False
For iCounter = 0 To UBound(vErrorArray)
With Target
Set rCell = .Find(vErrorArray(iCounter), LookIn:=xlValues,
lookat:=xlWhole)
If Not rCell Is Nothing Then
WorksheetErrors = True
Exit For
End If
End With
Next

Set rCell = Nothing
End Function

 

[Dave Peterson]

This checks to see what the formulas evaluate to:

Option Explicit
Sub testme()

Dim TestRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks

Set TestRng = Nothing
On Error Resume Next
Set TestRng = .Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "No errors in formulas!"
Else
MsgBox "You've got errors here: " & TestRng.Address(external:=True)
End If
End With

End Sub

 

[Redan]

Thanks Dave!!!

This checks to see what the formulas evaluate to:

Option Explicit
Sub testme()

Dim TestRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks

Set TestRng = Nothing
On Error Resume Next
Set TestRng = .Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "No errors in formulas!"
Else
MsgBox "You've got errors here: " &
TestRng.Address(external:=True)
End If
End With

End Sub

 

[Redan]

Hello Dave,

the code doesn't list #REF errors!

This checks to see what the formulas evaluate to:

Option Explicit
Sub testme()

Dim TestRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks

Set TestRng = Nothing
On Error Resume Next
Set TestRng = .Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "No errors in formulas!"
Else
MsgBox "You've got errors here: " &
TestRng.Address(external:=True)
End If
End With

End Sub

 

[Dave Peterson]

I started a test worksheet.

I put =A1 in C9

I deleted column A and ran the code and it showed the error.

Maybe you could be more specific.