Error checking row references

[sriche01]

Hello all!

I'm working on an error checking process for a large workbook. One of the errors we frequently encounter is formulas in the various worksheets that somehow get copied wrong, sorted wrong or something resulting in references that should be in the same row as the formula referencing a different row.

For instance: the following formula might be in row 22:
Correct: A22/E22
Incorrect: A22/E23

This is a simple example. Others may be criteria for index/match or sumifs or whatever.

What I am looking for is a search tool for specific columns to evaluate row references to see if they match the row the formula is in so I can correct this error. My thinking is it would look for non-absolute row references since most of these formulas are copied down and the non-absolute row references refer to the row the formula is in.

So I'm thinking it would be a VBA tool to loop through the cells in selected columns.

Any ideas? Have any of you put something like this together before?
Thanks!

 

[DRSteele]

I don't know how to give you a complete solution, but I do know a quick trick that might help.

Click on Formulas in the menu ribbon, and in the Formula auditing section click on Show Formulas. Using the mouse wheel or PageDown key, scan down the column at a reasonable pace and your eye just might capture any aberrant formulas.

 

[sriche01]

Thanks for the reply...
So I am writing a macro to search the cell precedents on the sheet I am evaluating.
I need a little help with the error handling.
I was hoping I could simply loop through all the cells in the sheet rather than predetermining the ones that have formulas.
Still in development, so I am testing it on one column, but when the for statement comes to a cell with no precedents, it throws an error. I'm having a hard time trapping the error. I want it to move to the next cell in the range.

Sub MyPrec()
    Dim wb As Workbook, ws As Worksheet
    Dim myRange As Range
    Dim rngPrecedents As Range, cell As Range
    Dim rngPrecedent As Range
    Dim row As Integer, lastrow As Integer
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Orders")

    
    lastrow = ws.Range("Order_Invoice_Num").Rows.Count
    
    On Error GoTo Errorhandler
    For row = ws.Range("Order_Invoice_Num").row To lastrow
        
        Set cell = ws.cells(row, 13)
        Debug.Print cell.Address

        Set rngPrecedents = cell.Precedents

        For Each rngPrecedent In rngPrecedents
            If rngPrecedent.row <> cell.row Then
                Debug.Print cell.Address
            End If
        Next rngPrecedent
       
Nextcell:
    Next row
    Exit Sub
    
Errorhandler:

   GoTo Nextcell
End Sub

So, according to the immediate window, the first error is trapped, but the second is not and a vba error box comes up. I've tried placing the On Error statement in different spots, going directly to Nextcell, etc. But the second error never is trapped. What am I doing wrong?

Alternatively, is there a way to say

If cell.Precedents has no cells found... goto Nextcell

but I don't know how to define that...
Thanks for any help!