Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Macro Code To Trace Dependents/Precedents

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro Code To Trace Dependents/Precedents

    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

    Code:
    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
    Attached Files

  • #2
    Re: Trace Dependents &amp; Msgbox If Cells Equal &quot;&quot;

    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
    Code:
    If Range("A" & RowCounter).Dependents = 0
    The Dependents property returns a range, so the test would be
    Code:
    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.

    Comment


    • #3
      Re: Trace Dependents &amp; Msgbox If Cells Equal &quot;&quot;

      Color or Format a Formula Referenced Cells - Precedents

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

      Comment


      • #4
        Re: Macro Code To Trace Dependents/Precedents

        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.

        Code:
        ' 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

        Comment


        • #5
          Re: Macro Code To Trace Dependents/Precedents

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

          Comment


          • #6
            Re: Macro Code To Trace Dependents/Precedents

            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

            Comment


            • #7
              Re: Macro Code To Trace Dependents/Precedents

              You could intialize RowCounter = 1 or

              Code:
              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.
              Code:
              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
              Last edited by mikerickson; June 18th, 2008, 09:54.

              Comment


              • #8
                Re: Macro Code To Trace Dependents/Precedents

                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...

                Code:
                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

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X