Prev: Sheet and workbook code: All sheets should have row 1 to 5 of Shee
Next: senin - sabtu
From: Jim Luedke on 24 Mar 2010 01:10
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.

***
From: Ron Rosenfeld on 24 Mar 2010 08:13
On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke <baobob(a)my-deja.com>
wrote:

>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
From: Ron Rosenfeld on 24 Mar 2010 09:36
On Wed, 24 Mar 2010 08:13:22 -0400, Ron Rosenfeld <ronrosenfeld(a)nospam.org>
wrote:


>>***
>
>For the address, perhaps:
>
>rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address
>
>--ron

Never Mind. That doesn't work

--ron
From: Rick Rothstein on 24 Mar 2010 12:56
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 (MVP - Excel)



"Jim Luedke" <baobob(a)my-deja.com> wrote in message
news:51588a88-75d9-4a99-ae3f-d2f15188f137(a)s20g2000prm.googlegroups.com...
> 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.
>
> ***

From: Ron Rosenfeld on 25 Mar 2010 06:37
On Wed, 24 Mar 2010 12:56:19 -0400, "Rick Rothstein"
<rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote:

>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 (MVP - Excel)

Rick,

1. I think the argument in the second line should be True.
2. Will this work in a function?
--ron
 | 
Pages: 1
Prev: Sheet and workbook code: All sheets should have row 1 to 5 of Shee
Next: senin - sabtu