VB Script to display cell dependent references

[lifisxtrmfun]

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]

Take a look at this

http://www.vbaexpress.com/forum/showthread.php?t=19348&highlight=precedent

[lifisxtrmfun]

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]

No break through yet ... any help ???

[mikerickson]

Do you have the FollowDirection argument of the .FollowArrows method set to the proper direction?

[lifisxtrmfun]

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]

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]

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