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 !

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

"Redan" wrote:

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 !

On Jan 24, 6:06 am, "Redan" wrote:
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

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 he " & TestRng.Address(external:=True)
End If
End With

End Sub


Redan wrote:

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 !









--

Dave Peterson

Thanks Dave!!!
"Dave Peterson" a écrit dans le message de
...
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 he " &
TestRng.Address(external:=True)
End If
End With

End Sub


Redan wrote:

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 !









--

Dave Peterson

Hello Dave,

the code doesn't list #REF errors!
"Dave Peterson" a écrit dans le message de
...
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 he " &
TestRng.Address(external:=True)
End If
End With

End Sub


Redan wrote:

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 !









--

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.

Redan wrote:

Hello Dave,

the code doesn't list #REF errors!
"Dave Peterson" a écrit dans le message de
...
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 he " &
TestRng.Address(external:=True)
End If
End With

End Sub


Redan wrote:

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 !









--

Dave Peterson

--

Dave Peterson