Finding error messages in a workbook

[bengamboni]

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

[Norman Jones]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Vic Eldridge]

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
>
>