Sunday, January 02, 2005

Locating errors in files

Here is the code I have recently developed to locate and report all the errors in a file.

To make this work I have created a sheet for the error list and named a range (single cell) on the sheet "cellerror_date". Two rows above that (with a blank row between) are the four headings, Sheet, Row, Column and Error Value.

Now add to a module in the Visual Basic Editor the follow statements.

Option Base 1
Dim LastRow&, LastCol%
Dim lastcellRowno, lastcellColno

Then add this function.
Function LastCell(ws As Worksheet) As Range
' Function written December 2004 by www.jethromanagement.biz
' Error-handling in case there is no data in the worksheet
On Error Resume Next
With ws
' Find the last real row
LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Find the last real column
LastCol% = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
End With
'initialize a Range object variable for the last populated row.
Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function

Finally add this procedure.
Sub find_errors()
' Macro written December 2004 by www.jethromanagement.biz
Dim x, z
Dim c
Dim cellerrors()

Application.ScreenUpdating = False
'clear out last error check results
Application.Goto reference:="cellerror_data"
Selection.CurrentRegion.Select
Selection.ClearContents

'cycle once to count errors
x = 1
For Each ws In Sheets
ws.Activate
'identify the last cell
lastcellRowno = LastCell(ActiveSheet).Row
lastcellColno = LastCell(ActiveSheet).Column
'select range to the last cell in worksheet
Range("A1").Select
Range(ActiveCell, ActiveCell.Offset(lastcellRowno - 1, lastcellColno - 1)).Select
'check if any cells have errors
For Each c In Selection.Cells
If IsError(c) Then
x = x + 1 'count number of errors
End If
Next c
Next ws

'cycle again pasting errors to array
ReDim cellerrors(x, 4)
z = 1
For Each ws In Sheets
ws.Activate
'identify the last cell
lastcellRowno = LastCell(ActiveSheet).Row
lastcellColno = LastCell(ActiveSheet).Column
'select range to the last cell in worksheet
Range("A1").Select
Range(ActiveCell, ActiveCell.Offset(lastcellRowno - 1, lastcellColno - 1)).Select
'check if any cells have errors - then add error data to array
For Each c In Selection.Cells
If IsError(c) Then
cellerrors(z, 1) = ws.Name
cellerrors(z, 2) = c.Row
cellerrors(z, 3) = c.Column
cellerrors(z, 4) = c.Formula
z = z + 1
End If
Next c
Next ws
'paste array into error worksheet
Application.Goto reference:="cellerror_data"
Range(ActiveCell, ActiveCell.Offset(x - 2, 3)).Select
Selection = cellerrors
Application.ScreenUpdating = False
End Sub

Run the macro titled find_errors to locate all errors on any sheet in the book (including hidden sheets) and record their location and value.


Posted by: Jethro | 1:45 PM |