Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding error messages in a workbook
Hi There, Just wondering if anybody knew the code to check through all cells in a workbook for any error messages then goto that cell so that the user can correct then proceed to the next error message? Kind of like a find function for any errors such as #N/A #Value! etc. Thanks, Ben -- bengamboni ------------------------------------------------------------------------ bengamboni's Profile: http://www.excelforum.com/member.php...o&userid=25378 View this thread: http://www.excelforum.com/showthread...hreadid=388578 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding error messages in a workbook
Hi Bengamboni,
The following macro locates all errors on the active sheet, color highlights them, and creates a timed report sheet listing rhe error cell addresses for audit / rectification purposes. Sub FindErrors() Dim RngA As Range, RngB As Range Dim RngBig As Range Dim rCell As Range Dim i As Long On Error Resume Next Set RngA = ActiveSheet.Cells. _ SpecialCells(xlCellTypeConstants, xlErrors) Set RngB = ActiveSheet.Cells. _ SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If Not RngA Is Nothing Then Set RngBig = RngA If Not RngB Is Nothing Then If Not RngBig Is Nothing Then Set RngBig = Union(RngB, RngBig) Else Set RngBig = RngB End If End If If Not RngBig Is Nothing Then Worksheets.Add ActiveSheet.Name = "Error Report" & _ Format(Now, "mm-dd-yy (hh mm)") For Each rCell In RngBig.Cells i = i + 1 ActiveSheet.Cells(i, 1).Value = _ rCell.Address(0, 0, , 1) Next rCell RngBig.Interior.ColorIndex = 6 Else MsgBox "No errors found" End If End Sub --- Regards, Norman "bengamboni" wrote in message ... Hi There, Just wondering if anybody knew the code to check through all cells in a workbook for any error messages then goto that cell so that the user can correct then proceed to the next error message? Kind of like a find function for any errors such as #N/A #Value! etc. Thanks, Ben -- bengamboni ------------------------------------------------------------------------ bengamboni's Profile: http://www.excelforum.com/member.php...o&userid=25378 View this thread: http://www.excelforum.com/showthread...hreadid=388578 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding error messages in a workbook
Hi Ben,
The following line will select all errors on the active worksheet. Application.Goto Cells.SpecialCells(xlCellTypeFormulas, 16) The following line will select the first error on the active worksheet. Application.Goto Cells.SpecialCells(xlCellTypeFormulas, 16).Cells(1) Add the characters ",True" to the end of either of the above lines and the spreadsheet will also scroll to selected cell. Preceed the command with "On Error Resume Next" to avoid the VBA error when no worksheet errors are found. Regards, Vic Eldridge "bengamboni" wrote: Hi There, Just wondering if anybody knew the code to check through all cells in a workbook for any error messages then goto that cell so that the user can correct then proceed to the next error message? Kind of like a find function for any errors such as #N/A #Value! etc. Thanks, Ben -- bengamboni ------------------------------------------------------------------------ bengamboni's Profile: http://www.excelforum.com/member.php...o&userid=25378 View this thread: http://www.excelforum.com/showthread...hreadid=388578 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Messages | Excel Worksheet Functions | |||
Error messages | Excel Discussion (Misc queries) | |||
Name error messages... | Excel Worksheet Functions | |||
Error Messages | Excel Worksheet Functions | |||
Pop-up Error Messages | Excel Programming |