Announcement

Collapse
No announcement yet.

Tracing Cell Precedents in VBA (Finding formulae t

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Tracing Cell Precedents in VBA (Finding formulae t



    I want to be able to determine if a particular cell has precedents on another sheet, or in another workbook. I had hoped to use .Precedents and do something like comparing "testCell.Precedents.Count" with "Intersect(Cells,testCell.Precedents).Count", or looking for "!" in testCell.Precedents.Address.

    However the .Precedents range only contains precedents on the same sheet, so this doesn't work. I have tried a couple of other tricks but so far can't find a catch-all workaround, (mainly because I some of the formula reference named cells on other sheets).

    Any help would be greatly appreciated, Dzinja.

  • #2
    Hi Dzinja,

    Try this routine,
    Code:
    Sub FindPrecedents()
    ' written by Bill Manville
    ' with edits from PaulS
      ' this procedure finds the cells which are the direct precedents of the active cell
      Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
      Dim stMsg As String
      Dim bNewArrow As Boolean
      Application.ScreenUpdating = False
      ActiveCell.ShowPrecedents
      Set rLast = ActiveCell
      iArrowNum = 1
      iLinkNum = 1
      bNewArrow = True
      Do
        Do
          Application.Goto rLast
          On Error Resume Next
          ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
          If Err.Number > 0 Then Exit Do
          On Error GoTo 0
          If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
          bNewArrow = False
          If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
            If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
              ' local
              stMsg = stMsg & vbNewLine & Selection.Address
            Else
              stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
            End If
          Else
            ' external
            stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
          End If
          iLinkNum = iLinkNum + 1  ' try another link
        Loop
        If bNewArrow Then Exit Do
        iLinkNum = 1
        bNewArrow = True
        iArrowNum = iArrowNum + 1  'try another arrow
      Loop
      rLast.Parent.ClearArrows
      Application.Goto rLast
      MsgBox "Precedents are" & stMsg
      Exit Sub
    End Sub
    Cheers
    Andy

    Cheers
    Andy

    Comment


    • #3
      Andy - now that's spooky, that was the exact routine that I was going to post! :o (Guess its something that doesn't come up that often)

      d - you may also like to look at the code in Stephen Bullen's FindCirc routine here :
      http://www.bmsltd.co.uk/Excel/Default.htm
      Cross-poster? Read this: Cross-posters
      Struggling to use tags (including Code tags)? : Forum tags

      Comment


      • #4


        As it happens there is a much easier way to do what I was trying to do which side-steps needing to know the precedents. However, it is good to know in case I need it in future.

        Many thanks, Dzinja

        Comment

        Working...
        X