VBA error checking formulas for row references

[sriche01]

Hello all,

This is long, but my main question is in bold at the bottom. Thanks!

A couple of days ago I put a concept out there asking for help setting up a vba tool to search through cells to determine if they are correctly referencing other cells within their row. Many sheets in my workbook contain columns of copy-down formulas like a sumifs or index/match referencing a key in the same row as the formula. Here's a typical example:

in O12 the following formula: =IF(M12="yes",0,IF(OR(E12="06",E12="08",E12="10",E12="18",E12="32")=TRUE,G12/E12,0))

or the more complex

=IF(COUNTIF(Item_Lookup,D12)>0,EOMONTH(DATE(YEAR(B12)-ROUNDDOWN((SUMIF(Item_Lookup,D12,Prod_Month)+SUMIF(Item_Lookup,D12,Grow_Months))/12,0)-IF(MONTH(B12)<(INDEX('Item Overview'!$A$4:$O$997,MATCH(D12,Item_Lookup,0),MATCH("Calendar Month when saleable",'Item Overview'!$A$3:$O$3,0))-1),1,0),INDEX('Item Overview'!$P$4:$P$997,MATCH(D12,Item_Lookup,0),1),1),0),"Non-Production Liner")

You'll notice all the references within the sheet are to another cell in the same row.

Occasionally there is an error in sorting, copying, or pasting and we'll get one or all of the references looking in the wrong row. This doesn't throw an error, so unless we closely scrutinize the result, we get bad data.

My goal is to create a macro that loops through all the cells with formulas in a given range and flag those referring to cells in a different row.

Here is what I have so far:

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, column As Integer, lastcolumn As Integer
    Dim adrs As String
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Orders")

    row = ws.Range("Order_Invoice_Num").row
    lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
    lastcolumn = ws.cells(row - 1, ws.Columns.Count).End(xlToLeft).column
  
  
  For column = 1 To lastcolumn
    For row = ws.Range("Order_Invoice_Num").row To lastrow
        On Error GoTo Errorhandler
        Set cell = ws.cells(row, column)
        If Left(cell.Formula2, 1) = "=" Then
        
        Set rngPrecedents = cell.Precedents

        For Each rngPrecedent In rngPrecedents
            
            If rngPrecedent.row <> cell.row Then
                If adrs <> cell.Address Then
                    adrs = cell.Address
                    Debug.Print adrs
                End If
            End If

        Next rngPrecedent
       End If
Nextcell:
    Next row
  Next column

This loops through all precedents in the range of rows and columns I have specified. As I understand the range.precedents property, it looks as deep as it can in references within the current sheet, which is great. Is there a way to make it evaluate only the immediate precedents? Those actually referred to in the formula, not the next level?

 

[sriche01]

so I discovered the range.directprecedents property. This is satisfactory for now. My only issue is that three columns have formulas that refer to vertical ranges within the worksheet I am on - blowing away the test of whether any direct precedents vary from the row the formula is in. I don't suppose anyone has thoughts on evaluating a formula like:

=COUNTIFS($D$3:$D$1814,D39,$Z$3:$Z$1814,"<"&Z39+1,$B$3:$B$1814,"<="&DATE(YEAR(B39),12,31),$B$3:$B$1814,">="&DATE(YEAR(B39),1,1))

located in AA39. The ranges cause a problem. I would like to check all precedents not defined in multiple cell ranges in the formula (in this case D39, Z39, and B39) to make sure they are in the same row as the formula itself. Obviously with the code above, every cell in AA would be triggered because of the multi row reference.

Is there a good way to peel these out of the formula in vba?

 

[Fluff]

How about

        For Each rngPrecedent In cell.DirectPrecedents.Areas
            If Not Rng.Count > 1 Then
               If rngPrecedent.row <> cell.row Then
                  '...
               End If
            End If
         Next rngPrecedent

 

[sriche01]

Thanks for the reply. To what does Rng refer?

 

[Fluff]

Oops, it should be rngPrecedent

 

[sriche01]

Ok thanks, I'll try that!

 

[sriche01]

Ok so here is my modified code, at least the loop part of it...

For row = ws.Range("Order_Invoice_Num").row To lastrow
        On Error GoTo Errorhandler
        Set cell = ws.cells(row, 26)  'checking column z because it is one with vertical range references
        If cell.HasFormula = True Then
        
        Set rngPrecedents = cell.DirectPrecedents 'this actually does nothing now

        For Each rngPrecedent In cell.DirectPrecedents.Areas
            If Not rngPrecedent.Count > 1 Then
            If rngPrecedent.row <> cell.row Then                                            'these lines add a hyperlink to the cell with bad referencing to my error checking worksheet
                If adrs <> cell.Address Then
                    adrs = cell.Address
                    ec.cells(logR, 8) = adrs
                    ec.Hyperlinks.Add Anchor:=ec.cells(logR, 8), _
                                Address:="", _
                                SubAddress:="Orders!" & adrs
                                
                    logR = logR + 1
                End If
            End If
            End If
        Next rngPrecedent
       End If
Nextcell:
    Next row

Good news: it avoided throwing an error for the vertical references. Bad news: it didn't find an intentional reference to a wrong row I had place in one of the cells. Still trying.

And thanks for the input!

 

[Fluff]

Try adding a debug.print line to see what the rngPrecedent address is.

 

[sriche01]

I'm sorry, I'm kind of struggling to see the path here. I assume we are trying to tell it "for each area within the direct precedents of the cell in question, if there are multiple cells in the area, skip it, otherwise map it." Am I right?

What line were you thinking of the debug.print?

 

[sriche01]

so after a little digging, there is a problem with the directprecedents.areas approach. If one reference is off by a row (the scenario we are trying to catch) and another reference in the formula is in the same column, the Areas property concatenates them into a range, even if they are in different parts of the formula. So that in a simple example:

=if(e8>0,e7*b7,b7) where e8 should have been e7, it says that one of the areas of directprecedents is e7:e8. Argh...