Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Macro Code To Trace Dependents/Precedents

  1. #1
    Join Date
    7th March 2008
    Posts
    6

    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

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,437

    Re: Trace Dependents & Msgbox If Cells Equal ""

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

  4. #4
    Join Date
    7th March 2008
    Posts
    6

    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd April 2007
    Posts
    3,437

    Re: Macro Code To Trace Dependents/Precedents

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2008
    Posts
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd April 2007
    Posts
    3,437

    Re: Macro Code To Trace Dependents/Precedents

    You could intialize RowCounter = 1 or

    VB:
    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.
    VB:
    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 at 09:54.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th March 2008
    Posts
    6

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

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Formula Auditing Of Precedents & Dependents
    By effang in forum EXCEL HELP
    Replies: 1
    Last Post: May 11th, 2008, 13:32
  2. Format Trace Precedents & Dependents Arrows & Lines
    By rjakhter in forum EXCEL HELP
    Replies: 1
    Last Post: December 19th, 2006, 12:36
  3. Trace Cell Precedents
    By Jack in the UK in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: March 3rd, 2003, 07:38

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno