VBA - Multiple Array-Enter Command


K

KKeenCA

I have a series of lengthy functions that are each dependent upon a
value in the same row. Copying the standard formula down is no
problem, however array formulas cannot be copied. I've generally just
copied the standard formula (that without the array enter gives a
#VALUE error) and then going through and array-entering
(ctrl+shift+enter) each cell. However, as my worksheets get more
complicated and my worksheets contain more and more array formulas,
array-entering each of them becomes time consuming. I've been
unsuccessful in writing a macro to help me out with this.

I'm effectively looking for the correct VBA language that would enable
a selected range of cells containing unique regular (i.e. not array)
formulas to each be "arran-entered" . Any ideas?
 
Ad

Advertisements

A

Andrew Taylor

Array formulas CAN be copied, though you need to make
sure the arget range doesn't include the original array
(otherwise you get the error "You cannot change part
of an array"

To define an array formula in VBA you can use the
FormulaArray property. E.g. to convert formula in the
active cell to an array formula:

ActiveCell.FormulaArray = ActiveCell.Formula

but as I said above you shouldn't need to do this just to
replicate AFs.

Andrew
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top