|
|
I'm sure this is really simple, but can't quite figure it out:
I have a UDF, to which I am passing a reference to a range of cells.
I would like to take the value in each cell and process it within the
UDF.
Say for example
R1C1 =MyTestLookup("TESTA","1") { result = 1; }
R1C2 =MyTestLookup("TESTB,"23") { result = 3; }
R1C3 =MyTestLookup("TESTC","8") { result = 6; }
A2 =MyUDF(R1C1:R1C3,arg1,arg2...)
Within MyUDF, if I use xlfFormula to ge the contents of R1C1,R1C2,or
R1C3 (passed via range argument) then it will return =MyTestLookup
(....).
Is there any way so that I can get MyUDF to read the results 1,3,6
contained in these cells instead of the formula?
Thanks,
Chris.
|
|
Jut wanted to add that although I have shown the MyTestLookup results
as numeric, they are actually text in my real code.
Chris.
On Dec 10, 2:19 pm, Chris <ch...[ at ]chrisbird.com> wrote:
[Quoted Text] > I'm sure this is really simple, but can't quite figure it out:
>
> I have a UDF, to which I am passing a reference to a range of cells.
> I would like to take the value in each cell and process it within the
> UDF.
>
> Say for example
>
> R1C1 =MyTestLookup("TESTA","1") { result = 1; }
> R1C2 =MyTestLookup("TESTB,"23") { result = 3; }
> R1C3 =MyTestLookup("TESTC","8") { result = 6; }
>
> A2 =MyUDF(R1C1:R1C3,arg1,arg2...)
>
> Within MyUDF, if I use xlfFormula to ge the contents of R1C1,R1C2,or
> R1C3 (passed via range argument) then it will return =MyTestLookup
> (....).
>
> Is there any way so that I can get MyUDF to read the results 1,3,6
> contained in these cells instead of the formula?
>
> Thanks,
>
> Chris.
|
|
No problems, found it out...
you need to use the xlCoerce function with no second argument - which
will return the single value from a SRef
- so in my case I need to convert the range into a series of cell
references and call xlCoerce for each one.
Chris.
On Dec 10, 2:40 pm, Chris <ch...[ at ]chrisbird.com> wrote:
[Quoted Text] > Jut wanted to add that although I have shown the MyTestLookup results
> as numeric, they are actually text in my real code.
>
> Chris.
>
> On Dec 10, 2:19 pm, Chris <ch...[ at ]chrisbird.com> wrote:
>
>
>
> > I'm sure this is really simple, but can't quite figure it out:
>
> > I have a UDF, to which I am passing a reference to a range of cells.
> > I would like to take the value in each cell and process it within the
> > UDF.
>
> > Say for example
>
> > R1C1 =MyTestLookup("TESTA","1") { result = 1; }
> > R1C2 =MyTestLookup("TESTB,"23") { result = 3; }
> > R1C3 =MyTestLookup("TESTC","8") { result = 6; }
>
> > A2 =MyUDF(R1C1:R1C3,arg1,arg2...)
>
> > Within MyUDF, if I use xlfFormula to ge the contents of R1C1,R1C2,or
> > R1C3 (passed via range argument) then it will return =MyTestLookup
> > (....).
>
> > Is there any way so that I can get MyUDF to read the results 1,3,6
> > contained in these cells instead of the formula?
>
> > Thanks,
>
> > Chris.- Hide quoted text -
>
> - Show quoted text -
|
|
On Dec 10, 2:54 pm, Chris <ch...[ at ]chrisbird.com> wrote:
[Quoted Text] > No problems, found it out...
>
> you need to use the xlCoerce function with no second argument - which
> will return the single value from a SRef
> - so in my case I need to convert the range into a series of cell
> references and call xlCoerce for each one.
>
Can't you just coerce once to an xlTypeMulti and avoid having to do a
bunch of coercions?
(and you want to avoid having your function registered as macro mode -
# - unless you REALLY need that formula, and then watch out for calc
tree, and volatility with R and U args).
Lee.
> Chris.
>
> On Dec 10, 2:40 pm, Chris <ch...[ at ]chrisbird.com> wrote:
>
> > Jut wanted to add that although I have shown the MyTestLookup results
> > as numeric, they are actually text in my real code.
>
> > Chris.
>
> > On Dec 10, 2:19 pm, Chris <ch...[ at ]chrisbird.com> wrote:
>
> > > I'm sure this is really simple, but can't quite figure it out:
>
> > > I have a UDF, to which I am passing a reference to a range of cells.
> > > I would like to take the value in each cell and process it within the
> > > UDF.
>
> > > Say for example
>
> > > R1C1 =MyTestLookup("TESTA","1") { result = 1; }
> > > R1C2 =MyTestLookup("TESTB,"23") { result = 3; }
> > > R1C3 =MyTestLookup("TESTC","8") { result = 6; }
>
> > > A2 =MyUDF(R1C1:R1C3,arg1,arg2...)
>
> > > Within MyUDF, if I use xlfFormula to ge the contents of R1C1,R1C2,or
> > > R1C3 (passed via range argument) then it will return =MyTestLookup
> > > (....).
>
> > > Is there any way so that I can get MyUDF to read the results 1,3,6
> > > contained in these cells instead of the formula?
>
> > > Thanks,
>
> > > Chris.- Hide quoted text -
>
> > - Show quoted text -
|
|
|