Finding Cell Address of All Errors
 

We produce building cost estimates for architectural clients. We have a
quality control macro that checks and corrects pagination, adjusts column
widths, rezooms each page, and positions the cursor to cell A1 on each sheet,
and checks for #VALUE# and #REF# errors. The number of errors for each sheet
is written to an integer array and later displayed:

' Assign errors array
aintErrors(ActiveSheet.Index) = _
Application.Evaluate("SUM(--(ISERROR(" & ActiveSheet.UsedRange.Address &
")))")

Users are now requesting that the cell addresses of each error be obtained
to simplify tracking them down. Can anyone help me do this?

Thank you.

Sprinks

Finding Cell Address of All Errors
 

Try selecting a range and then pressing F5, clicking Special. Then click on
the box for formulas and uncheck all but the errors box. The error cells
will then be found.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Sprinks" wrote in message
...
We produce building cost estimates for architectural clients. We have a
quality control macro that checks and corrects pagination, adjusts column
widths, rezooms each page, and positions the cursor to cell A1 on each
sheet,
and checks for #VALUE# and #REF# errors. The number of errors for each
sheet
is written to an integer array and later displayed:

' Assign errors array
aintErrors(ActiveSheet.Index) = _
Application.Evaluate("SUM(--(ISERROR(" & ActiveSheet.UsedRange.Address &
")))")

Users are now requesting that the cell addresses of each error be obtained
to simplify tracking them down. Can anyone help me do this?

Thank you.

Sprinks

Finding Cell Address of All Errors
 

Thanks, Bob.

"Bob Flanagan" wrote:

Try selecting a range and then pressing F5, clicking Special. Then click on
the box for formulas and uncheck all but the errors box. The error cells
will then be found.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Sprinks" wrote in message
...
We produce building cost estimates for architectural clients. We have a
quality control macro that checks and corrects pagination, adjusts column
widths, rezooms each page, and positions the cursor to cell A1 on each
sheet,
and checks for #VALUE# and #REF# errors. The number of errors for each
sheet
is written to an integer array and later displayed:

' Assign errors array
aintErrors(ActiveSheet.Index) = _
Application.Evaluate("SUM(--(ISERROR(" & ActiveSheet.UsedRange.Address &
")))")

Users are now requesting that the cell addresses of each error be obtained
to simplify tracking them down. Can anyone help me do this?

Thank you.

Sprinks

Finding Cell Address of All Errors
 

Sub WriteErrors()

'writes error cells coordinates
'to a text file
'C:\sheetName_errors.txt

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set f = fso.OpenTextFile _
("c:\" & ActiveSheet.Name _
& "_errors.txt", 2, True)

Count = 0
f.WriteLine "Errors in " _
& ActiveSheet.Name & " :"
f.WriteLine

For Each c In ActiveSheet. _
UsedRange.Cells

If IsError(c.Value) Then
Count = Count + 1
f.WriteLine "Cell (" & c.Row _
& ", " & c.Column & ")"
End If

Next

If Count = 0 Then
f.WriteLine "No errors in this sheet"
End If

f.Close

End Sub


--
urkec


"Sprinks" wrote:

We produce building cost estimates for architectural clients. We have a
quality control macro that checks and corrects pagination, adjusts column
widths, rezooms each page, and positions the cursor to cell A1 on each sheet,
and checks for #VALUE# and #REF# errors. The number of errors for each sheet
is written to an integer array and later displayed:

' Assign errors array
aintErrors(ActiveSheet.Index) = _
Application.Evaluate("SUM(--(ISERROR(" & ActiveSheet.UsedRange.Address &
")))")

Users are now requesting that the cell addresses of each error be obtained
to simplify tracking them down. Can anyone help me do this?

Thank you.

Sprinks

Finding Cell Address of All Errors
 

Terrific, urkec; that's what I was looking for.

Thanks for your help.

Sprinks

"urkec" wrote:

Sub WriteErrors()

'writes error cells coordinates
'to a text file
'C:\sheetName_errors.txt

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set f = fso.OpenTextFile _
("c:\" & ActiveSheet.Name _
& "_errors.txt", 2, True)

Count = 0
f.WriteLine "Errors in " _
& ActiveSheet.Name & " :"
f.WriteLine

For Each c In ActiveSheet. _
UsedRange.Cells

If IsError(c.Value) Then
Count = Count + 1
f.WriteLine "Cell (" & c.Row _
& ", " & c.Column & ")"
End If

Next

If Count = 0 Then
f.WriteLine "No errors in this sheet"
End If

f.Close

End Sub


--
urkec


"Sprinks" wrote:

We produce building cost estimates for architectural clients. We have a
quality control macro that checks and corrects pagination, adjusts column
widths, rezooms each page, and positions the cursor to cell A1 on each sheet,
and checks for #VALUE# and #REF# errors. The number of errors for each sheet
is written to an integer array and later displayed:

' Assign errors array
aintErrors(ActiveSheet.Index) = _
Application.Evaluate("SUM(--(ISERROR(" & ActiveSheet.UsedRange.Address &
")))")

Users are now requesting that the cell addresses of each error be obtained
to simplify tracking them down. Can anyone help me do this?

Thank you.

Sprinks