What VBA function returns cell 2 from which cell 1 gets its value?

[Jim Luedke]

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

 

[Ron Rosenfeld]

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron

 

[Ron Rosenfeld]

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron

Never Mind. That doesn't work

--ron

 

[Rick Rothstein]

If your formula is as simple as you show (only one range reference), then
the answer is probably as simple as this...

.ShowPrecedents
Set Cel2= Cel1.NavigateArrow(False, 1, 1)
.ShowPrecedents True

If you have other range references in your formula, and especially if those
references are for multiple sheets, then the code gets more complicated as
the 2nd and 3rd arguments have to account for them.

 

[pbart]

Cel1.Precedents.Select

will activate a range which is the union of cells appearing in the formula
(Excel 2007).

If you have trace arrows showing on your sheet then NavigateArrow will allow
you to select a particular one to follow.

 

[Rick Rothstein]

As far as I know, that will only work for Precedent cells on the same
worksheet as the cell (Cel1 in this case)... Precedents will not work on
cells referencing other sheets. The only way I know to do that is how I
showed in my post... show the precedent arrows, then use NavigateArrow to
find the cell on the foreign sheet that you want and the turn the arrows
off. The code for this can get complicated if there are several cells on
other sheets, especially if there are multiple other sheets involved.

 

[Ron Rosenfeld]

If your formula is as simple as you show (only one range reference), then
the answer is probably as simple as this...

.ShowPrecedents
Set Cel2= Cel1.NavigateArrow(False, 1, 1)
.ShowPrecedents True

If you have other range references in your formula, and especially if those
references are for multiple sheets, then the code gets more complicated as
the 2nd and 3rd arguments have to account for them.

Rick,

1. I think the argument in the second line should be True.
2. Will this work in a function?
--ron

 

[pbart]

Rick

You are right; my apologies!

A different strand of thinking could be to cheat and record the address of
predecessor cell (SalesSheet!$A$1) as a string on ProfitSheet at the time the
data is set up; the string could be used in the formula
"=MassageData(INDIRECT(Cel3)) much as one would a database 'foreign key'. I
would only suggest this as a potential solution if it fits well into the
spirit of the workbook in question.