Code to check errors in excel 2002
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 ! |
Code to check errors in excel 2002
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 ! |
Code to check errors in excel 2002
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 |
Code to check errors in excel 2002
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 |
Code to check errors in excel 2002
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 |
Code to check errors in excel 2002
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 |
Code to check errors in excel 2002
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 |
All times are GMT +1. The time now is 12:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com