View Full Version : VB Script to display cell dependent references
lifisxtrmfun
03-07-2012, 08:16 AM
Hi,
I am novice to excel programming.
Here is the scenario of a problem i am trying to find -
I have a spread sheet with 3 sheets "Sheet 1", "Sheet 2" and "Sheet 3"
"Sheet 1" - has the following data
A1: Test 1
A2: Test 2
A3: Test 3
"Sheet 2" has a the following references
A2: Sheet 1!A2
A1: Sheet 1!A3
"Sheet 3" has a the following references
A2: Sheet 1!A1
A3: Sheet 1!A3
If I run "Audit" trace dependency on "Sheet 1".A3 i see a graph and the grid showing its bee used on 'Sheet 2!A1' and 'Sheet 3!A3' that is good. What I am trying is to get that displayed as a text on a secondary column (say C$) for all A$. I tired different ways but I could not get to the reference list and display it.
If the spread sheet is small probably i would have used toolbar audit trace dependency but i have spread sheet with thousands and i need to make sure all the entries were referred.
Can any one suggest 'how to' or sample reference code.
Appreciate your help and thanks in advance.
- MS
mikerickson
03-07-2012, 08:51 AM
Take a look at this
http://www.vbaexpress.com/forum/showthread.php?t=19348&highlight=precedent
lifisxtrmfun
03-09-2012, 03:26 PM
Thanks Mike for your suggestion. The solution looked to be too complex, probably I did not get it.
So, this is what I tried and it does list the dependent cell reference but no all.
Sub Trace_Dependents()
Dim wSheetIndex As Integer, rowCounter As Integer, idx As Integer
Dim refString As String, currCell As Range
rowCounter = 1
wSheetIndex = 3
Do
'Initiate continue on error
On Error Resume Next
'If cell in column A is not blank
If Not (Sheets(wSheetIndex).Range("A" & rowCounter) = 0 Or Sheets(wSheetIndex).Range("A" & rowCounter).Value = vbNullString) Then
Set currCell = Sheets(wSheetIndex).Range("A" & rowCounter)
'If no dependents goto skip code
On Error GoTo skip
Debug.Print ("Current Selection Address: " & currCell.Address(, , , True))
refString = ""
With Sheets(wSheetIndex).Range("A" & rowCounter)
.ShowDependents
.NavigateArrow False, 1, 1
For Each c In Selection.Cells
refString = refString & c.Address(, , , True)
Debug.Print ("Dep: " & refString)
Next
Sheets(wSheetIndex).Range("C" & rowCounter).Value = refString
'rowCounter = rowCounter + 1
End With
'Remove trace precedents
With ActiveCell
.ShowPrecedents
.NavigateArrow True, 1, 1
.ShowPrecedents (True)
For Each c In Selection.Cells
refString = refString & c.Address(, , , True)
Debug.Print ("Pre: " & refString)
Next
rowCounter = rowCounter + 1
End With
Else
rowCounter = rowCounter + 1
End If
skip:
If Err Then
MsgBox "HELL ... you got an ERROR !!! " & Err.Description
Err.Clear
End If
Loop Until rowCounter > 5
On Error GoTo 0
End Sub
The exec show the following ..
Test 1 [Book1.xlsm]Sheet1!$A$2
Test 2 [Book1.xlsm]Sheet1!$A$3
Test 3 [Book1.xlsm]Sheet3!$A$3
Where as it supposed to show -
Test 1 [Book1.xlsm]Sheet1!$A$2 [Book1.xlsm]Sheet3!$A$2
Test 2 [Book1.xlsm]Sheet1!$A$3
Test 3 [Book1.xlsm]Sheet3!$A$3
Any ideas or help ?
- MS
lifisxtrmfun
03-15-2012, 09:41 AM
No break through yet ... any help ???
mikerickson
03-15-2012, 02:42 PM
Do you have the FollowDirection argument of the .FollowArrows method set to the proper direction?
lifisxtrmfun
03-16-2012, 12:33 PM
Mike,
FlowDirection is set right, when I trace it down step by step the audit map (through graph table) show it fine and I see all the references (multiple), here is the screen shot -
[Stripped due to insufficient post count]
Not sure why the code base "selection" of dependents shows only the 1st one and ignores the rest.
- MS
mikerickson
03-16-2012, 05:11 PM
You have to loop through the second argument of .NavigateArrrow. Here's a micro test routine to show the technique. (Put =Sheet2!A1+Sheet2!B1 in Sheet1!A1, to set up the test)
Sub test()
Dim i As Long
With Sheet1.Range("A1")
.ShowPrecedents
i = 1
On Error Resume Next
Do
.NavigateArrow True, 1, i
MsgBox ActiveCell.Address(, , , True)
i = i + 1
Loop Until Err
End With
End Sub
Note that if there isn't an off sheet reference in A1's formula, then different techniques are needed.
lifisxtrmfun
03-17-2012, 11:14 AM
Mike you rock ! - It WORKS :-)
I tried this approach but did not use the iterator counter on the second param.
Looks like that is the key to the solutions.
Thanks a lot and appreciate your time.
Thanks
MS
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.