Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro to find and log all formula errors on all sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default 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


Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I find duplicate information on 2 sheets using a formula? KaseyJo Excel Discussion (Misc queries) 1 January 11th 10 07:46 PM
Macro - 2 Sheets - Find/Insert/Copy/Paste shorticake Excel Programming 0 March 12th 07 09:21 PM
FIND AND REPLACE DATA BETWEEN TWO EXCEL SHEETS USING FORMULA gkb Excel Discussion (Misc queries) 4 December 7th 06 09:41 AM
Stopping errors when a find statement doesn't find! matpj[_34_] Excel Programming 3 January 25th 06 02:23 PM
add-in macro strange errors--method sheets of object workbook fail DavidH[_2_] Excel Programming 3 November 4th 04 05:49 AM


All times are GMT +1. The time now is 07:56 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement