×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Range methods: NavigateArrow, precedents, dependents

Range methods: NavigateArrow, precedents, dependents

Range methods: NavigateArrow, precedents, dependents

(OP)
If you have a handy subroutine using the range methods (NavigateArrow, Precedents, Dependents), could you possibly post the sub here?
We would all appreciate it.

RE: Range methods: NavigateArrow, precedents, dependents

Could you be more specific?

RE: Range methods: NavigateArrow, precedents, dependents

(OP)
Any working sub example that includes NavigateArrow method of Range Object would be a help.
Like:

myRange2.showprecedents
precORdependents = 1
set myRange1= myRange2.NavigateArrow(precORdependents, arrowNo, LinkNo)

debug.print myRange1.Formula
debug.print myrange1.Address(External:=1)

give the cells referred to in the formula of myRange2, which is what I need.

The aim here is to dump all the formulas in a worksheet, their precedents and their dependents.
precORdependent = 1 traces precedents, =0 traces dependents.

What I cannot find specifically is how to ask for number of links per arrow.

Thanks

RE: Range methods: NavigateArrow, precedents, dependents

Mala (Computer) Aug 23, 2001
Tigrek, sorry I'm a bit late.

When limits are indefinite, it's a good idea to set up loops with preposterously high upper-bounds and sit back comfortably while the VBA 'On Error Resume Next' statement handles things - may not be very elegant, but gets the work done:

To test the code for your problem I did the following:

1. Created a formula in cell F6 on a sheet (can be any cell, but the code below refers to this - the cell has been named 'TestRng').

2. This formula had ALL sorts of references - same worksheet, other worksheets in same workbook AND other Workbooks.

Taking off from your code, I wrote the following Sub:

Sub MyPrecedents()
  RoOff = -1
  Set TestRng = Range("TestRng")
  Application.Goto Reference:=TestRng, Scroll:=True
  TestRng.ShowPrecedents
  For Arr = 1 To 20
    For Lnk = 1 To 20
      On Error Resume Next
      Application.Goto Reference:=TestRng, Scroll:=True
      Set PrecRng = TestRng.NavigateArrow(True, Arr, Lnk)
      If Err = 0 Then
        KK = PrecRng.Address(External:=True)
        MatchFound = 0
        MatchFound = WorksheetFunction.Match(KK, Columns("G").Cells, 0)
        If MatchFound = 0 Then
          RoOff = RoOff + 1
          TestRng.Offset(RoOff, 1) = KK
        End If
      End If
    Next
  Next
End Sub

Note that I set Arr to 20 and Lnk per Arr to 20 - Nobody in their right mind would write such a long formula - so it's a safe bet it'll cover ALL references.

The references are 'dumped' in the col. beside TestRng starting from TestRng.Row

To modify for Dependents, modify the statement:
Set PrecRng = TestRng.NavigateArrow(True, Arr, Lnk)
and
TestRng.Offset(RoOff, 1) = KK

TO

Set DepRng = TestRng.NavigateArrow(False, Arr, Lnk)
+ all refs to PrecRng to DepRng
and
TestRng.Offset(RoOff, 2) = KK to get the refs in the next col.

Of course, u'll have to modify the code to suit the way your worksheet is set up.

By the way, this was a good one - and the most satisfying part is the real effort you'd put in before posting the query on the forum.

Good Luck!

RE: Range methods: NavigateArrow, precedents, dependents

(OP)
Thanks

It worked beautiful.

RE: Range methods: NavigateArrow, precedents, dependents

Mala-

This looks like it works, but I think it works because you are keeping track of which cells you've visited in the result column and checking each visited cell against that (with the .Match), rather than because of the On Error Resume Next statement.  I think that the spirit of the original question (and of *my* question!!) is how to traverse that list to the end and the stop *without* having to keep track of where you've been!!

I'm working on a similar problem, except that I want to traverse the list and at each cell in the list, repeat the traverse, recursively, and I can't figure out how to stop!!

Here's what I have so far (assuming the set of cells whose formula's I want to list is in TestRng):

***********

Public depth, fnum

Dim oCell As Range

Sub showSome(oCell As Range)
    Dim aCell As Range
    
    For i = 0 To depth
        Print #fnum, Spc(1);
    Next i
        
    If oCell.HasFormula Then
        Print #fnum, oCell.Address(external:=True); Spc(1); oCell.Formula
    Else
        Print #fnum, oCell.Address(external:=True)
    End If
        
    
    If oCell.HasFormula Then
        depth = depth + 1
        oCell.ShowPrecedents (False)
    
        For i = 1 To 20
            Set aCell = oCell.NavigateArrow(True, i)
            showSome oCell:=aCell
        Next
    
        oCell.ShowPrecedents (True)
    End If
    
    depth = depth - 1
End Sub

Sub showAllContents()
    Dim oStart As Range
    theDir = ActiveWorkbook.Path
    ChDrive (Left(theDir, 1))
    ChDir (theDir)
    fnum = FreeFile()
    Open theDir & "\sheet.txt" For Output Access Write As #fnum
    
    For Each oSht In Worksheets
        oSht.ClearArrows
    Next
    
    depth = 0
    For Each oStart In Range("TestRng")
        showSome oCell:=oStart
    Next
    Close fnum
End Sub


*********

So the idea is that ShowAllContents() calls ShowSome() for each cell in the range, and that ShowSome() calls itself recursively until there are no links left.  It looks like what happens, is that if there are, say, 3 links from a given cell, then on NavigateArrow(True,4) you end up at the same place as on NavigateArrow(True,1), which isn't so great.... any ideas?  Is this totally unclear??  :)

Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources