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" <re...@gmail.com> 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 here: " & 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" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed)...
> 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 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" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed)...
> 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 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" <(E-Mail Removed)> a écrit dans le message de
> news:(E-Mail Removed)...
> > 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 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