Tracing Precedents

[adam2079]

Hi everyone,

I'm new to this site so please let me know if I haven't supplied the correct information

I have a standard workbook containing 3 sheets and have the following formula in Cell H7 on Sheet 1

=D11+SUM(Sheet3!A1:A3)+E13+F15+Sheet2!H8+SUM(G13:G15)

What I'm trying to do is to display all the precedents that make up the formula in to cell J9 onwards.

The code I currently have is as follows:

Private Sub CommandButton1_Click()
Dim TheText As String
Dim TheName As String
Dim TheCounter As Integer
Dim iArrowNum As Integer
Dim iLinkNum As Integer
Dim i As Integer

Range("J9:J100").ClearContents
Range("H7").ShowPrecedents
TheCounter = 9
iArrowNum = 1
iLinkNum = 1
TheName = ActiveSheet.Name

Do
On Error Resume Next
Range("H7").NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum _
, LinkNumber:=iLinkNum

If Err.Number > 0 Then Exit Do
If ActiveSheet.Name <> TheName Then
TheText = ActiveSheet.Name & "!" & ActiveCell.Address
Cells(TheCounter, 10) = TheText
TheCounter = TheCounter + 1
End If
Sheets("Sheet1").Activate
iLinkNum = iLinkNum + 1
Loop
Range("J7") = Range("H7").Precedents.Count
If Range("H7").Precedents.Count = 0 Then Exit Sub
For i = 1 To Range("H7").Precedents.Count
Range("H7").Precedents(i).Select
Cells(TheCounter, 10) = ActiveSheet.Name & "!" & ActiveCell.Address
TheCounter = TheCounter + 1
Next i
ActiveSheet.ClearArrows
End Sub

The results I'm trying to obtain in cell J9, J10 etc is:

Sheet3!$A$1
Sheet3!$A$2
Sheet3!$A$3
Sheet2!$H$8
Sheet1!$D$11
Sheet1!$E$13
Sheet1!$F$15
Sheet1!$G$13
Sheet1!$G$14
Sheet1!$G$15

but I'm actually getting

Sheet3!$A$1
<TABLE style="WIDTH: 84pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=112 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4778" width=112><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 84pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" width=112 height=14>Sheet2!$H$8
Sheet1!$D$11
Sheet1!$D$12
Sheet1!$D$13
Sheet1!$D$14
Sheet1!$D$15
Sheet1!$D$16


</TD></TR></TBODY></TABLE>

I've tried many different ways of doing this for the past few hours and have drawn a blank.

Can anyone help me?

Many Thx

 

[adam2079]

Hi again,

I've just realised that my problem is more advanced than i first thought. Essentially what I'm trying to do is read a huge accounts pack that has many different formulas aside from those listed in my previous post.

The formulas may then lead to other formulas and then to even more formulas before finally picking up a balance from a particular cell or cells.

So as an example if I had a cell with a balance of £100, is there a way of tracing through the spreadsheet to find which cell or cells that balance was picked up from so I could then add a comment to the cell listing those cell references?

I hope that makes sense

Any input would be greatly appreciated

 

[adam2079]

Hi chaps,

After many hours of work I've finally managed to get the code to do what I want. It ain't pretty but I don't think it was ever going to be