Thorny VBA problem. Any creative solution appreciated.

[[email protected]]

I understand that functions can only modify the cell they are called
from. However, if anyone has a solution to this problem, I would
greatly appreciate it.

A function F(N) does a vlookup in a table and returns a value.

If it cannot find N in the table, I want to add N to a list in
another table.

F() is called from an addin if that is relevant.

Any creative solution appreciated.

Thanks
Jeff Stryer

[Tim Williams]

Your only solution is likely to be to use something along the lines of
an event procedure (such as "calculate") instead of a UDF.


If F() is called from an add-in, then where is the table to be
modifed? In the activeworkbook?

Tim.


<[email protected]> wrote in message
news:[email protected]...
>I understand that functions can only modify the cell they are called
> from. However, if anyone has a solution to this problem, I would
> greatly appreciate it.
>
> A function F(N) does a vlookup in a table and returns a value.
>
> If it cannot find N in the table, I want to add N to a list in
> another table.
>
> F() is called from an addin if that is relevant.
>
> Any creative solution appreciated.
>
> Thanks
> Jeff Stryer
>

[[email protected]]

Yes, the table to be modified is in the active workbook.

Tim Williams wrote:
> Your only solution is likely to be to use something along the lines
of
> an event procedure (such as "calculate") instead of a UDF.
>
>
> If F() is called from an add-in, then where is the table to be
> modifed? In the activeworkbook?
>
> Tim.
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> >I understand that functions can only modify the cell they are called
> > from. However, if anyone has a solution to this problem, I would
> > greatly appreciate it.
> >
> > A function F(N) does a vlookup in a table and returns a value.
> >
> > If it cannot find N in the table, I want to add N to a list in
> > another table.
> >
> > F() is called from an addin if that is relevant.
> >
> > Any creative solution appreciated.
> >
> > Thanks
> > Jeff Stryer
> >

[Stephen Bullen]

Hi Jeff

> I understand that functions can only modify the cell they are called
> from. However, if anyone has a solution to this problem, I would
> greatly appreciate it.
>
> A function F(N) does a vlookup in a table and returns a value.
>
> If it cannot find N in the table, I want to add N to a list in
> another table.
>
> F() is called from an addin if that is relevant.
>
> Any creative solution appreciated.

You might be able to adapt one of my posts on **** Kusleika's 'Daily
Dose of Excel' blog:

http://www.*****-blog.com/archives/2...hat-do-things/

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk