Resolving a Circular Reference

[sdruley]

I have been struggling for days trying to find the source of a circular reference. I tried the following code but it just sits there doing nothing. Can anyone find the glitch in this code or perhaps give me a better tool?

Sub FindCircRefs()
      
       Set Source = ActiveSheet.Range("study")
       ' Get source information.
       Sourcesheet = ActiveSheet.Name
       
       Sheets.Add
       ' Get destination information.
       destsheet = ActiveSheet.Name
       destrange = ActiveCell.Address
       ' Return to source.
       Worksheets(Sourcesheet).Activate
       RowCount = 0
       ' Trap for error in "result", indicating no circular reference.
       On Error GoTo notcircular


       ' Loop through every used cell in source.
       For Each Item In Source
           ' Check to see if cell contains a formula.
           If Left(Item.Formula, 1) = "=" Then
               ' If cell intersects with precedents, cell has circular
               ' reference.
               result = Intersect(ActiveSheet.Range(Item.Address), _
                   ActiveSheet.Range(Item.Precedents.Address))


               Worksheets(destsheet).Range(destrange).Offset(RowCount, _
                   0).Value = Item.Address(False, False)


               Worksheets(destsheet).Range(destrange).Offset(RowCount, _
                   1).Value = "'" & Item.Formula


               RowCount = RowCount + 1
               ' Skip to here if not circular.
skipitem:
           End If
           Next
       Exit Sub


   ' If error in "result", go here.
notcircular:
       ' Skip cells that do not contain circular references.
      Resume skipitem
   End Sub

 

[AlphaFrog]

The code wouldn't find nested indirect circular references where say cell A1 references cell A2 which in turn references cell A3 which references back to cell A1.

These links may help.

Remove or allow a circular reference

Excel 2010 has a tool to help track CRs down.
Circular References in Excel 2010 Formulas

 

[Eric W]

There's a couple options. From the Excel Ribbon, choose Formulas, Error Checking, Circular References.

From VBA you can code

Set BadCell = Worksheets("Sheet1").CircularReference

Which will select the first cell with a circular reference.

 

[sdruley]

Thank you gentlemen for your help. I guess I don't have a circular reference after all.

There's a couple options. From the Excel Ribbon, choose Formulas, Error Checking, Circular References.

From VBA you can code

Set BadCell = Worksheets("Sheet1").CircularReference

Which will select the first cell with a circular reference.

 

[Pinaceous]

Hi Guys,

I've removed my ribbon and there is no shortcut key for Error Checking in regard to finding the Circular Reference, that I can find.

I've tried the vba code above to search for my Circular Reference and no luck either.

Is there another vba code that can find a Circular Reference?

Thanks,
pinaceous