Trace and cycle through dependents

amilnathwani

New Member
Joined
Oct 15, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello

I am looking for some code so I can create a shortcut to help me trace and cycle through each of the dependents to a formula in my excel workbook.

I know Excel has the 'Ctrl [', but this doesn't allow you to cycle through all of the different dependents to a formula across different worksheets.

So ideally, I would have something that allows me trace the first dependent (eg via Ctrl G) to the formula, and then cycle through each and every other dependent (eg via Ctrl N) contained within the same formula. And then a final shortcut to help me return back to the original cell containing the formula (eg Ctrl H).

Any help would be really greatly appreciated!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello

I am looking for some code so I can create a shortcut to help me trace and cycle through each of the dependents to a formula in my excel workbook.

I know Excel has the 'Ctrl [', but this doesn't allow you to cycle through all of the different dependents to a formula across different worksheets.

So ideally, I would have something that allows me trace the first dependent (eg via Ctrl G) to the formula, and then cycle through each and every other dependent (eg via Ctrl N) contained within the same formula. And then a final shortcut to help me return back to the original cell containing the formula (eg Ctrl H).

Any help would be really greatly appreciated!
Welcome to Mr Excel.

A precedent is a cell that provides data to a formula.

A dependent is a cell that contains a formula which refers to other cells.

Do you want to select a cell that contains a formula and identify the cells that that formula depends upon (the precedent cells) or do you want to select a cell and
identify all references to that cell (the dependent cells).
 
Upvote 0
Welcome to Mr Excel.

A precedent is a cell that provides data to a formula.

A dependent is a cell that contains a formula which refers to other cells.

Do you want to select a cell that contains a formula and identify the cells that that formula depends upon (the precedent cells) or do you want to select a cell and
identify all references to that cell (the dependent cells).
Thank you for your reply.

The former - so I want to select a cell and then identify the cells that the formula depends upon. However I want to be able use a shortcut to cycle through all of the precedent cells (or ranges if applicable) one at a time.

Hope that helps clarify.

Thanks
 
Upvote 0
Thank you for your reply.

The former - so I want to select a cell and then identify the cells that the formula depends upon. However I want to be able use a shortcut to cycle through all of the precedent cells (or ranges if applicable) one at a time.

Hope that helps clarify.

Thanks
Not something that Excel does but I have some code that I can alter to provide what you want.

Do you want to loop through the sheets containing precedents in any particular order or in the order in which they appear in the formula.
 
Upvote 0
Not something that Excel does but I have some code that I can alter to provide what you want.

Do you want to loop through the sheets containing precedents in any particular order or in the order in which they appear in the formula.
Thank you so much.

Just the order in which they appear in the formula would be great!
 
Upvote 0
No worries at all, appreciate the help
This is Work In Progress but works for a number of formula that I have set up.

I have used Ctrl+q to enable the user to select a cell containing a formula and then to ascertain where the Precedents are.

Ctrl-w will run code to loop through each worksheet containing the Precedents and select the appropriate cells.

It places the top left cell of the selected cells at the top and left of the window.

You will need to set up you own macros.

I have used some code provided by others online. This has been credited.

Let me know how you get on.

VBA Code:
Option Explicit

Public arrPrecedents() As Variant
Public intCurrent As Integer

Sub storeprecedents()
'
' storeprecedents Macro
'
' Keyboard Shortcut: Ctrl+q
'
   ' Select cell with formula.
   
   ' Place this line of code in your own macro.
   
   Call subFindPrecedents
   
End Sub

Sub displayprecedents()
'
' displayprecedents Macro
'
' Keyboard Shortcut: Ctrl+w
'

  ' Display Precedents.
  
  ' Place these lines of code in your own macro.
  
  If intCurrent = UBound(arrPrecedents) Then
    intCurrent = 1
  Else
    intCurrent = intCurrent + 1
  End If
  
  Selection.Cells(1).Select
    
  Worksheets(Range(arrPrecedents(intCurrent)).Parent.Name).Activate
  
  Range(arrPrecedents(intCurrent)).Select

  If Range(arrPrecedents(intCurrent)).Cells(1).Row <> 1 Then
    ActiveWindow.ScrollRow = Range(arrPrecedents(intCurrent)).Cells(1).Row - 1
  End If
    
  If Range(arrPrecedents(intCurrent)).Cells(1).Column <> 1 Then
    ActiveWindow.ScrollColumn = Range(arrPrecedents(intCurrent)).Cells(1).Column - 1
  End If

End Sub

Public Sub subFindPrecedents()
Dim rngSelected As Range
Dim i As Integer

  ActiveWorkbook.Save

  Set rngSelected = fncSelectCell
  
  If rngSelected Is Nothing Then
    Exit Sub
  End If
 
  If Not rngSelected.HasFormula Then
    Exit Sub
  End If
    
  Worksheets(rngSelected.Parent.Name).Activate
  
  Erase arrPrecedents
      
  arrPrecedents = fncCellPrecedents(rngSelected)
  
  intCurrent = LBound(arrPrecedents)

  Worksheets(rngSelected.Parent.Name).Activate
  
End Sub

' https://stackoverflow.com/questions/11320626/does-excel-have-a-built-in-method-for-parsing-formulas-ie-to-obtain-a-list-of
Public Function fncCellPrecedents(rngCell As Range) As Variant()
Dim colResultRanges As New Collection
Dim varResultRangeArray() As Variant
Dim strFormula As String
Dim strElements() As String
Dim count As Integer
Dim i As Integer

  If rngCell.Cells.count <> 1 Or rngCell.HasFormula = False Then
    Exit Function
  End If
    
  strFormula = Mid(rngCell.Formula, 2, Len(rngCell.Formula) - 1)

  If fncIsRange(strFormula) Then
    colResultRanges.Add Range(strFormula), 1
  Else
        
    strFormula = Replace(strFormula, "(", " ")
    
    strFormula = Replace(strFormula, ")", " ")
        
    strElements() = fncSplitMultiDelims(strFormula, " +-*/\^")
        
    For i = LBound(strElements) To UBound(strElements)
      
      If fncIsRange(Trim(strElements(i))) Then
        
        If rngCell.Parent.Name = Range(strElements(i)).Parent.Name Then
          strElements(i) = rngCell.Parent.Name & "!" & strElements(i)
        End If
  
        colResultRanges.Add Trim(strElements(i))
        
        count = count + 1
      
      End If
    
    Next
    
  End If
  
  ReDim varResultRangeArray(1 To colResultRanges.count)
    
  For i = 1 To colResultRanges.count
    varResultRangeArray(i) = CStr(colResultRanges(i))
  Next

  fncCellPrecedents = varResultRangeArray

End Function

Public Function fncIsRange(var As Variant) As Boolean
Dim rng As Range

  On Error Resume Next
  Set rng = Range(var)
  
  If Err.Number = 0 Then
    fncIsRange = True
  End If
  
  On Error GoTo 0
  
End Function

' **********************************************************************************************
' fncSplitMultiDelims by Alain Bryden

' https://www.experts-exchange.com/articles/1480/How-to-Split-a-String-with-Multiple-Delimiters-in-VBA.html
' **********************************************************************************************
Private Function fncSplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
        Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional ByVal Limit As Long = -1) As String()
Dim ElemStart As Long, N As Long, M As Long, Elements As Long
Dim lDelims As Long, lText As Long
Dim arr() As String
    
    lText = Len(Text)
    lDelims = Len(DelimChars)
    If lDelims = 0 Or lText = 0 Or Limit = 1 Then
        ReDim arr(0 To 0)
        arr(0) = Text
        fncSplitMultiDelims = arr
        Exit Function
    End If
    ReDim arr(0 To IIf(Limit = -1, lText - 1, Limit))
    
    Elements = 0: ElemStart = 1
    For N = 1 To lText
      If InStr(DelimChars, Mid(Text, N, 1)) Then
        arr(Elements) = Mid(Text, ElemStart, N - ElemStart)
        If IgnoreConsecutiveDelimiters Then
            If Len(arr(Elements)) > 0 Then Elements = Elements + 1
        Else
          Elements = Elements + 1
        End If
        ElemStart = N + 1
        If Elements + 1 = Limit Then Exit For
      End If
    Next N
    
    If ElemStart <= lText Then arr(Elements) = Mid(Text, ElemStart)
    
    If IgnoreConsecutiveDelimiters Then If Len(arr(Elements)) = 0 Then Elements = Elements - 1
    
    ReDim Preserve arr(0 To Elements)
    
    fncSplitMultiDelims = arr
    
End Function

Private Function fncSelectCell() As Range
Dim rngSelected As Range

  Do While True
  
    On Error Resume Next
    Set rngSelected = Application.InputBox( _
      Title:="Range selection", _
      Prompt:="Select a single cell containing a formula.", _
      Type:=8)
    On Error GoTo 0
  
    If rngSelected Is Nothing Then
      Exit Do
    End If
  
    If rngSelected.CountLarge = 1 And rngSelected.HasFormula Then
      Set fncSelectCell = rngSelected
      Exit Do
    End If
  
  Loop
  
End Function
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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