Macro to find and log all formula errors on all sheets

[Adnan]

Is there a code that goes thru each sheet of the work book and looks for
formula errors (any type) and then record the name of the file and type of
error into a different workbook?

I have a workbook that has a list of paths to lots of other workbooks, in
this workbook I want to create a macro that open each and everyone of those
workbooks listed and look for any type of formula errors (i.e.: #VALUE or
#DIV/O, or #N/A etc…) in them.

Your help is greatly appreciated,
Adnan

 

[Barb Reinhardt]

Try this

Sub Test()
Dim aWB As Workbook
Dim myWB As Workbook
Dim r As Range

Set aWB = ActiveWorkbook
Set myWB = Workbooks.Add
Set myWS = myWB.Worksheets(1)
myWS.Name = "Errors Found"
lrow = 1
myWS.Cells(1, 1) = "Workbook Name"
myWS.Cells(1, 2) = "Worksheet Name"
myWS.Cells(1, 3) = "Cell Address"
myWS.Cells(1, 4) = "Cell Value"
myWS.Cells(1, 5) = "Cell Formula"

For Each WS In aWB.Worksheets
For Each r In WS.UsedRange
If IsError(r) Then
lrow = lrow + 1
myWS.Cells(lrow, 1) = aWB.Name
myWS.Cells(lrow, 2) = WS.Name
myWS.Cells(lrow, 3) = r.Address
myWS.Cells(lrow, 4) = r.Value
myWS.Cells(lrow, 5) = "'" & r.FormulaR1C1
End If
Next r
Next WS

End Sub

 

[Adnan]

Barb,

Thanks for your help --- This code works great but finds errors only in the
work book that is open, I need something like this:
http://cid-642741f4bfb02015.skydrive.live.com/self.aspx/Public/FindError.xls

Thank you,
Adnan

 

[Barb Reinhardt]

I'm going to assume that you have the path names listed in column A starting
in Row 2.


Sub Test()
Dim aWB As Workbook
Dim oWB As Workbook
Dim oWS As Worksheet
Dim myRange As Range
Dim lRow As Range
Dim myLink As Range
Dim myWB As Workbook
Dim r As Range

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet
Set myRange = aWS.Cells(2, 1) 'Sets beginning of range of links
lRow = aWS.Cells(aWS.Rows.Count, myRange.Row).End(xlUp).Row
Set myRange = myRange.Resize(lRow - myRange.Row + 1, 1)

Set myWB = Workbooks.Add
Set myWS = myWB.Worksheets(1)
myWS.Name = "Errors Found"
lRow = 1
myWS.Cells(1, 1) = "Workbook Name"
myWS.Cells(1, 2) = "Worksheet Name"
myWS.Cells(1, 3) = "Cell Address"
myWS.Cells(1, 4) = "Cell Value"
myWS.Cells(1, 5) = "Cell Formula"


For Each myLink In myRange
Set oWB = Workbooks.Open(myLink.Value)
If Not oWB Is Nothing Then
For Each WS In oWB.Worksheets
For Each r In WS.UsedRange
If IsError(r) Then
lRow = lRow + 1
myWS.Cells(lRow, 1) = aWB.Name
myWS.Cells(lRow, 2) = WS.Name
myWS.Cells(lRow, 3) = r.Address
myWS.Cells(lRow, 4) = r.Value
myWS.Cells(lRow, 5) = "'" & r.FormulaR1C1
End If
Next r
Next WS
oWB.Close
End If
Next myLink




End Sub

 

[Adnan]

Barb,

I'm gettin an error. See it here:
http://cid-642741f4bfb02015.skydrive.live.com/self.aspx/Public/OfficeCommunity/OnSheets.xls

v/r
Adnan

 

[Barb Reinhardt]

I don't make it a habit to download worksheets from the net. What is the
error and where? I'm guessing it's in the workbook.open line.

Before that, put
debug.print mylink.address, "value=";mylink.value
on error resume next
after the workbook open put
on error goto 0

 

[Barb Reinhardt]

Change myRange.row to myRange.column.

 

[Adnan]

Still not working, same error.

Change myRange.row to myRange.column.

 

[Per Jessen]

Hi

Change this line :

Dim lRow As Range

to

Dim lRow As Long

Regards,
Per

 

[Adnan]

Thank you Per, it worked.

Hi

Change this line :

Dim lRow As Range

to

Dim lRow As Long

Regards,
Per