ExcelBanter - Macro to find and log all formula errors on all sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro to find and log all formula errors on all sheets (https://www.excelbanter.com/excel-programming/415749-macro-find-log-all-formula-errors-all-sheets.html)

Adnan

Macro to find and log all formula errors on all sheets

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

Macro to find and log all formula errors on all sheets

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Adnan

Macro to find and log all formula errors on all sheets

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Macro to find and log all formula errors on all sheets

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Adnan

Macro to find and log all formula errors on all sheets

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Macro to find and log all formula errors on all sheets

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Adnan

Macro to find and log all formula errors on all sheets

It primts with an error debug message 'Object variale or with block variable
not set'. When hit Debug, it takes me to this line of code: lRow =
aWS.Cells(aWS.Rows.Count, myRange.Row).End(xlUp).Row

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Macro to find and log all formula errors on all sheets

Change myRange.row to myRange.column.
--
HTH,
Barb Reinhardt

"Adnan" wrote:

It primts with an error debug message 'Object variale or with block variable
not set'. When hit Debug, it takes me to this line of code: lRow =
aWS.Cells(aWS.Rows.Count, myRange.Row).End(xlUp).Row

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Adnan

Macro to find and log all formula errors on all sheets

Still not working, same error.

"Barb Reinhardt" wrote:

Change myRange.row to myRange.column.
--
HTH,
Barb Reinhardt

"Adnan" wrote:

It primts with an error debug message 'Object variale or with block variable
not set'. When hit Debug, it takes me to this line of code: lRow =
aWS.Cells(aWS.Rows.Count, myRange.Row).End(xlUp).Row

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Per Jessen

Macro to find and log all formula errors on all sheets

Hi

Change this line :

Dim lRow As Range

to

Dim lRow As Long

Regards,
Per

"Adnan" (donotspam) skrev i meddelelsen
...
Still not working, same error.

"Barb Reinhardt" wrote:

Change myRange.row to myRange.column.
--
HTH,
Barb Reinhardt

"Adnan" wrote:

It primts with an error debug message 'Object variale or with block
variable
not set'. When hit Debug, it takes me to this line of code: lRow =
aWS.Cells(aWS.Rows.Count, myRange.Row).End(xlUp).Row

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

Adnan

Macro to find and log all formula errors on all sheets

Thank you Per, it worked.

"Per Jessen" wrote:

Hi

Change this line :

Dim lRow As Range

to

Dim lRow As Long

Regards,
Per

"Adnan" (donotspam) skrev i meddelelsen
...
Still not working, same error.

"Barb Reinhardt" wrote:

Change myRange.row to myRange.column.
--
HTH,
Barb Reinhardt

"Adnan" wrote:

It primts with an error debug message 'Object variale or with block
variable
not set'. When hit Debug, it takes me to this line of code: lRow =
aWS.Cells(aWS.Rows.Count, myRange.Row).End(xlUp).Row

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

Barb,

I'm gettin an error. See it he
http://cid-642741f4bfb02015.skydrive...y/OnSheets.xls

v/r
Adnan

"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt

"Adnan" wrote:

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.../FindError.xls

Thank you,
Adnan

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

"Adnan" wrote:

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

All times are GMT +1. The time now is 08:56 AM.