Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Cell address by value | Excel Discussion (Misc queries) | |||
Finding Cell address by value | Excel Discussion (Misc queries) | |||
Finding the Address of a Cell | Excel Worksheet Functions | |||
finding the address of a specific cell? | Excel Programming | |||
Finding cell address... | Excel Programming |