Error iterating through all arrows of ShowDependents

Amateurhr

Active Member
Joined
Dec 26, 2011
Messages
343
I'm trying to find all the precedents of a cell (on all sheets) and return them in a collection. I use a collection so I can weed out the same cell if it were to show up in another function that I call FindDependents from. The code below only returns 1 cell from another sheet (should be 3) and returns a duplicate cell on the same sheet. Any ideas where my mistake is?

Code:
Function FindDependents(ByVal rngPrecedent As range) As Collection

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Find all of the cells that are dependent on the precedent cell,
    ' but are on a different sheet to the precedent cell.
    ' Returns a collection of ranges
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim collDependents As Collection, dependentCell As range
    Dim arrowNumber As Integer, keyStr As String

    Set collDependents = New Collection

    'We'll be checking for errors as we go
    On Error Resume Next
    
    'Show the dependency arrows
    rngPrecedent.ShowDependents
    arrowNumber = 1
    
    'We'll break out of this loop when no more dependencies are found.
    Do
        Set dependentCell = rngPrecedent.NavigateArrow(False, arrowNumber, 1)
        If Err.Number <> 0 Then
            GoTo NoMoreArrows
        Else
            'Add dependent to Collection of arrows
            keyStr = dependentCell.parent.name & "!" & dependentCell.Address
            collDependents.add Item:=dependentCell, key:=keyStr
            
            'Check the next arrow.
            arrowNumber = arrowNumber + 1
        End If
    Loop While True
    
NoMoreArrows:
    Set FindDependents = collDependents
    Exit Function

End Function
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I don't understand this line:
Code:
Loop While True
Should it be:
Code:
Loop while not dependentCell is nothing
 
Upvote 0
The Do loop runs into it hits an error, in which the Goto NoMoreArrows takes over and exits the loop.

I think your suggestion is sound, but is not the reason why this isn't working. I have noticed that...

Code:
Set dependentCell = rngPrecedent.NavigateArrow(False, arrowNumber, 1)

...seems to return the same cell for arrowNumber 3, 4, 5... even though they should be different cells.
 
Upvote 0

Forum statistics

Threads
1,203,666
Messages
6,056,640
Members
444,879
Latest member
suzndush

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top