Finding error messages in a workbook
 

Hi There,

Just wondering if anybody knew the code to check through all cells in a
workbook for any error messages then goto that cell so that the user can
correct then proceed to the next error message?

Kind of like a find function for any errors such as #N/A #Value! etc.

Thanks,

Ben


--
bengamboni
------------------------------------------------------------------------
bengamboni's Profile: http://www.excelforum.com/member.php...o&userid=25378
View this thread: http://www.excelforum.com/showthread...hreadid=388578

Finding error messages in a workbook
 

Hi Bengamboni,

The following macro locates all errors on the active sheet, color highlights
them, and creates a timed report sheet listing rhe error cell addresses for
audit / rectification purposes.

Sub FindErrors()
Dim RngA As Range, RngB As Range
Dim RngBig As Range
Dim rCell As Range
Dim i As Long

On Error Resume Next

Set RngA = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlErrors)
Set RngB = ActiveSheet.Cells. _
SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not RngA Is Nothing Then Set RngBig = RngA

If Not RngB Is Nothing Then
If Not RngBig Is Nothing Then
Set RngBig = Union(RngB, RngBig)
Else
Set RngBig = RngB
End If
End If

If Not RngBig Is Nothing Then
Worksheets.Add
ActiveSheet.Name = "Error Report" & _
Format(Now, "mm-dd-yy (hh mm)")
For Each rCell In RngBig.Cells
i = i + 1
ActiveSheet.Cells(i, 1).Value = _
rCell.Address(0, 0, , 1)
Next rCell
RngBig.Interior.ColorIndex = 6

Else
MsgBox "No errors found"

End If

End Sub




---
Regards,
Norman



"bengamboni" wrote
in message ...

Hi There,

Just wondering if anybody knew the code to check through all cells in a
workbook for any error messages then goto that cell so that the user can
correct then proceed to the next error message?

Kind of like a find function for any errors such as #N/A #Value! etc.

Thanks,

Ben


--
bengamboni
------------------------------------------------------------------------
bengamboni's Profile:
http://www.excelforum.com/member.php...o&userid=25378
View this thread: http://www.excelforum.com/showthread...hreadid=388578

Finding error messages in a workbook
 

Hi Ben,

The following line will select all errors on the active worksheet.
Application.Goto Cells.SpecialCells(xlCellTypeFormulas, 16)

The following line will select the first error on the active worksheet.
Application.Goto Cells.SpecialCells(xlCellTypeFormulas, 16).Cells(1)

Add the characters ",True" to the end of either of the above lines and the
spreadsheet will also scroll to selected cell.

Preceed the command with "On Error Resume Next" to avoid the VBA error when
no worksheet errors are found.


Regards,
Vic Eldridge



"bengamboni" wrote:


Hi There,

Just wondering if anybody knew the code to check through all cells in a
workbook for any error messages then goto that cell so that the user can
correct then proceed to the next error message?

Kind of like a find function for any errors such as #N/A #Value! etc.

Thanks,

Ben


--
bengamboni
------------------------------------------------------------------------
bengamboni's Profile: http://www.excelforum.com/member.php...o&userid=25378
View this thread: http://www.excelforum.com/showthread...hreadid=388578