circular referecs - excel cannot caculate a formula. there is a circular...references that cause it cannot be listed for you

[daveyc18]

ridiuclous....pops up when i insert rows (bottom left corner says "circular references"

but each sheet i go to there is no cell indicated

i tried even using this

Sub Find_Circular_References_In_All_Worksheets()
    Dim vItem, result, ws As Worksheet, sh As Worksheet, dRng As Range
    Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
    Set dRng = sh.Range("A1")
    sh.DisplayRightToLeft = False
    For Each ws In ThisWorkbook.Worksheets
        On Error GoTo errHandler
        If ws.Name <> sh.Name Then
            With ws
                For Each vItem In .UsedRange
                    If Left(vItem.Formula, 1) = "=" Then
                        result = Intersect(.Range(vItem.Address), .Range(vItem.Precedents.Address))
                
                        dRng.Offset(, 0).Value = ws.Name
                        dRng.Offset(, 1).Value = vItem.Address(False, False)
                        dRng.Offset(, 2).Value = "'" & vItem.Formula
                        Set dRng = dRng.Offset(1)
                    End If
Skipper:
                Next vItem
            End With
        End If
    Next ws
    sh.Columns.AutoFit
    Exit Sub
errHandler:
    Resume Skipper
End Sub

but it finds nothing! freaking excel....

 

[Alex Blakenburg]

Do you have any formulas in Named Ranges ?

 

[daveyc18]

Do you have any formulas in Named Ranges ?

yes, but none of them have a ref error ..

 

[Alex Blakenburg]

If it had a #REF error it wouldn't producing a circular reference.
Example below:
Range name used in C2 but formula in Range name refers to C2 > Circular Ref error

 

[daveyc18]

If it had a #REF error it wouldn't producing a circular reference.
Example below:
Range name used in C2 but formula in Range name refers to C2 > Circular Ref error

View attachment 76917

nothing....but what do you know? i opened the file this morning and no circular references! only if that were the case last night and i could have finished my work faster

at any rate, thanks for your replies

 

[Alex Blakenburg]

No worries. Glad it sorted itself out for you.