VBA - Multiple Array-Enter Command

[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?

 

[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