Writing Array Formulas in VBA

E

Ed

Anyone know how to assign an array formula to a cell
using VBA? If possible, I'd like to use 'A1' format.
Follow on question: Complicating matters, the formula has
quotes in it - how do I force the quotes through without
VBA bugging out due to the extra quotes I'm putting
around the formula
 
R

Rob van Gelder

Ed,

ActiveCell.FormulaArray = "=1+1"

Not sure what you mean in your second question.
Could you supply an example?

Rob
 
D

Doug Glancy

Ed,

I sometimes find the easiest way to get the correct quote configuration is
to enter the formula into a cell with the macro recorder turned on, and then
copy the resulting code.

hth,

Doug
 
D

Dana DeLouis

If I understand your second question, I believe you need to use double
quotes around text...

Range("B1").Formula = "=If(A1>0,""Yes"",""No"")"
 
D

Dave Peterson

And try it using the A1 reference style.

the help is incorrect with this statement:

If you use this property to enter an array formula, the formula must use the
R1C1 reference style, not the A1 reference style (see the second example).
 
Top