Automatically select cells based on results size

[heverp]

Hi,

I am using a VBA addin to get data from a database. The user selects a
cell then enters a function name with parameters in the formula bar.
This retrieves the data from the database based on what was specified.

The problem I'm having is that the user must know beforehand what the
size of the results will be or at least guess at it. This requires the
user to select a large range of cells and run the formula over the
entire range.

The ideal solution would be for the user to select a single cell, run
the query and the addin would then extend the range to accommodate the
result set.

So far I have tried to achieve this and failed. I have looked around a
lot online and found no solution to this. Can anyone help me out here,
any solution to this would be very much appreciated.

Thanks,

-Pete

 

[Pete]

Hi,

I am using a VBA addin to get data from a database. The user selects a
cell then enters a function name with parameters in the formula bar.
This retrieves the data from the databasebasedon what was specified.

The problem I'm having is that the user must know beforehand what thesizeof theresultswill be or at least guess at it. This requires the
user toselecta large range ofcellsand run the formula over the
entire range.

The ideal solution would be for the user toselecta single cell, run
the query and the addin would then extend the range to accommodate the
result set.

So far I have tried to achieve this and failed. I have looked around a
lot online and found no solution to this. Can anyone help me out here,
any solution to this would be very much appreciated.

Thanks,

-Pete

In case anyone is interested in this, here is the response I got from
Microsoft (handy having a dedicated support contact!):

Unfortunately I don't think this is possible. I'm not an expert on
user defined functions, but I believe one of their limitations is that
they cannot place values in cells other than the cell, or range, that
contains the formula. However, you can do this with a macro.