Referencing cells that array covers
 

Within VB, how do you reference the cells that your array formula i
entered in?

e.g. Range A1 to A10 has an array formula entered into it. Within th
code, I need to identify that this covers 10 cells.

NB 'Selection.Count' is not valid since this only works at the time th
user enters the array formula, and not on subsequent recalcs when othe
cells may be active

--
mayr
-----------------------------------------------------------------------
mayre's Profile: http://www.excelforum.com/member.php...fo&userid=2760
View this thread: http://www.excelforum.com/showthread.php?threadid=47129

Referencing cells that array covers
 

If you record a macro when you do:

Select A1, then
edit|goto|special
you can click Current array.

You'll end up with code that resolves to something like:

MsgBox Range("a1").CurrentArray.address
or
MsgBox Range("a1").CurrentArray.cells.count




mayre wrote:

Within VB, how do you reference the cells that your array formula is
entered in?

e.g. Range A1 to A10 has an array formula entered into it. Within the
code, I need to identify that this covers 10 cells.

NB 'Selection.Count' is not valid since this only works at the time the
user enters the array formula, and not on subsequent recalcs when other
cells may be active.

--
mayre
------------------------------------------------------------------------
mayre's Profile: http://www.excelforum.com/member.php...o&userid=27605
View this thread: http://www.excelforum.com/showthread...hreadid=471297

--

Dave Peterson