Macro Code To Trace Dependents/Precedents

[gtrdude485]

I would like to trace dependents of all cells <> "" in column A of a sheet "Tickmarks". If there is a cell in column A which has a value, but does not have a dependent I would like that value to be stored. When the trace dependents check is done for all the cells with values in column A, I would like to display a msgbox with all the cell values collected above.

Eventually, I will be trying to locate the cells with no dependents but with values in Column A and replace them with cells with dependents and change the dependent links to the updated cells. I have attached an example.

Thanks

Sub Trace_Dependents_Tickmark_Sheet() 
     
    Dim RowCounter As Integer 
    RowCounter = 3 
     
     'Select firsct cell with t/m & show dependents
    Range("A" & RowCounter).ShowDependents 
     
     'Loop until RowCounter = 200
    Do 
        If Range("A" & RowCounter).Value = 0 Then 
            RowCounter = RowCounter + 1 
             
        Else 
             'On Error Resume Next
            Range("A" & RowCounter).ShowDependents 
             'This is where I get an error;  I'm not sure what to put...
            If Range("A" & RowCounter).Dependents = 0 Then msgbox ("This tickmark has no dependents: " & Range("A" & RowCounter)) 
            RowCounter = RowCounter + 2 
             'On Error GoTo msgbox
             'msgbox:
             '           msgbox ("This tickmark has no dependents: " & Range("A" & RowCounter - 2).Value)
        End If 
    Loop Until RowCounter > 200 
     
     'Show dependents of last cell
    Range("A" & RowCounter).ShowDependents 
     
     'Go back to top of page
    Range("A3").Select 
End Sub 

[mikerickson]

There is a problem with your situation. All the dependents of the sheet are cells on a different sheet. That means that the .Dependents property won't work. You need to use the .NavigateArrows method to find the dependents you want.

Here is a link to a routine I wrote for Precedents.
http://vbaexpress.com/forum/showthre...591#post143591

The first argument of .NavigateArrow will need to be changed and .ShowDependents used instead of .ShowPrecedents.
Fortunatly, tracing dependents doesn't require parsing a formula to find the references to closed workbooks.

I hope this helps.

P.S. This is the part that is erroring

If Range("A" & RowCounter).Dependents = 0 

The Dependents property returns a range, so the test would be

If Range("A" & RowCounter).Dependents Is Nothing 

But, .Dependents only returns those dependent cells that are on the same sheet as the first cell, so you will get false results from your sample sheet.

[Dave Hawley]

Color or Format a Formula Referenced Cells - Precedents

Color or Format a Formula Referenced Cells - Precedents Should help.

[gtrdude485]

I have been able to navigate back and forth between sheets but I'm getting an error when trying to access a value of a cell. I have changed up the code a little. The file in the first post still applies.

 ' Trace&Replace_Dependents Macro
 ' Traces and replaces unused Dependents for Tickmark Sheet
 '
Sub Trace_Replace_Dependents_Tickmark_Sheet() 
     
    Dim sh As Worksheet 
    Dim tmSheetIndex As Integer, RowCounter As Integer 
    RowCounter = 0 
     
    For Each sh In Worksheets 
        If sh.Name = "Tickmarks" Then tmSheetIndex = sh.Index 
    Next sh 
     
     'Select first t/m and go to first link
    Do 
         'I get the error here
        If Sheets(tmSheetIndex).Range("A" & RowCounter).value = 0 Or Sheets(tmSheetIndex).Range("A" & RowCounter).value = "" Then 
            RowCounter = RowCounter + 1 
            msgbox "Row counter is" & RowCounter 
             
        Else 
            On Error Goto dependenterror 
            With Sheets(tmSheetIndex).Range("A" & RowCounter) 
                .ShowDependents 
                .naviagtearrow False, 1, 1 
            End With 
             
            With ActiveCell 
                .ShowPrecedents 
                .NavigateArrow True, 1, 1 
                .ShowPrecedents (False) 
                RowCounter = RowCounter + 2 
            End With 
             
        End If 
    Loop Until RowCounter > 12 
     
dependenterror: 
    msgbox "hello" 
End Sub 

[mikerickson]

RowCounter = 0 on the first pass so Range("A" & RowCounter) is erroring.

[gtrdude485]

Crap! OK now that I have that done, I couldn't seem to find any help on code that will allow me to display a msgbox on an error, as above, but will allow me to continue the Do loop... Maybe I'm just missing the gap between on error goto "some code on error" and on error goto next

[mikerickson]

You could intialize RowCounter = 1 or

Do 
    On Error Resume Next 
    If Not (Range("A"&rowCounter")=0 Or Range("A"&RowCounter)=vbNullString) Then 
        On Error Goto dependenterror 
        With Sheets(tmSheetIndex).Range("A" & RowCounter) 
            .ShowDependents 
            .naviagtearrow False, 1, 1 
        End With 
        With ActiveCell 
            .ShowPrecedents 
            .NavigateArrow True, 1, 1 
            .ShowPrecedents (False) 
            RowCounter = RowCounter + 2 
        End With 
    Else 
        RowCounter = RowCounter + 1 
        msgbox "Row counter is" & RowCounter 
    End If 
     
Loop Until RowCounter > 12 

EDIT
I just re-read your post. If the message box you are refering to is the "Hello" box, try this.

Do 
    On  Error Resume Next 
    If Not ( Range("A"&rowCounter")=0 Or Range("A"&RowCounter)=vbNullString) Then 
        On Error Goto skip 
        With  Sheets(tmSheetIndex).Range("A" & RowCounter) 
            .ShowDependents 
            .naviagtearrow False, 1, 1 
        End With 
        With ActiveCell 
            .ShowPrecedents 
            .NavigateArrow True, 1, 1 
            .ShowPrecedents (False) 
            RowCounter = RowCounter + 2 
        End With 
    Else 
        RowCounter = RowCounter + 1 
        msgbox "Row counter is" & RowCounter 
    End If 
Skip: 
    If Err Then 
        Msgbox "Hello" 
        Err.Clear 
    End If 
Loop Until RowCounter > 12 
On Error Goto 0 

[gtrdude485]

I'm having a problem with the loop recognizing if there is an error. I have the code writing "this t/m is attached" if the the cell in column A has dependents into the cell in column J. However, the code writes this to every cell in J with a value in Column A. Also, It doesn't generate the msgbox. It doesn't seem like the code is detecting the error...

Sub Trace_Replace_Dependents_Tickmark_Sheet() 
     
    Dim sh As Worksheet 
    Dim tmSheetIndex As Integer, RowCounter As Integer 
    RowCounter = 1 
     
    For Each sh In Worksheets 
        If sh.Name = "Tickmarks" Then tmSheetIndex = sh.Index 
    Next sh 
     
    Do 
         
         'Initiate continue on error
        On Error Resume Next 
         
         'If cell in column A is not blank
        If Not (Sheets(tmSheetIndex).Range("A" & RowCounter) = 0 Or Sheets(tmSheetIndex).Range("A" & RowCounter).value = vbNullString) Then 
             
             'If no dependents goto skip code
            On Error Goto skip 
             
             'Trace first dependent
            With Sheets(tmSheetIndex).Range("A" & RowCounter) 
                .ShowDependents 
                .NavigateArrow False, 1, 1 
            End With 
             
             'Remove trace precedents and return to tickmarks page
            With ActiveCell 
                .ShowPrecedents 
                .NavigateArrow True, 1, 1 
                .ShowPrecedents (True) 
                 
                 'writes if t/m is attached and updates RowCounter
                Sheets(tmSheetIndex).Range("J" & RowCounter) = "This t/m is attached" 
                RowCounter = RowCounter + 1 
            End With 
             
        Else 
            RowCounter = RowCounter + 1 
             
        End If 
         
skip: 
        If Err Then 
            msgbox "hello" 
            Err.Clear 
        End If 
         
    Loop Until RowCounter > 55 
    On Error Goto 0 
End Sub