FormulaArray alternative for A1 reference style?

[KR]

I've always used A1 reference style in all my code, formulas, etc. I guess
my brain is just wrapped that way.

Now I need to use VBA code to insert an array formula, because the source
ranges are changed when new data are added, and I need to reset the array
formula to the original ranges again. however, my A1 notation doesn't play
well with FormulaArray.

Is there an alternative? Or, if I use R1C1 notation for just this formula,
will that affect anything else in the workbook?

Should I set my array formulas to use named ranges instead of straight
worksheet references, and use VBA to refresh the named ranges instead?

Thanks!
Keith

 

[Die_Another_Day]

Setting a cells formula using the FormulaR1C1 command will have no
effect on the rest of the workbook. I use this style quite often to
achieve dynamic ranges.

Charles

 

[Guest]

FormulaArray does not require R1C1 style addressing and if you use it, it
won't affect anything adversely.

Just to demonstrate from the immediate window:

ActiveCell.FormulaArray = "=Sum((A1:A10=B1)*(C1:C10=D1))"
? activeCell.Formula
=SUM((A1:A10=B1)*(C1:C10=D1))

ActiveCell.FormulaArray = "=Sum((R1C1:R10C1=R1C2)*(R1C3:R10C3=R1C4))"
? ActiveCell.Formula
=SUM(($A$1:$A$10=$B$1)*($C$1:$C$10=$D$1))


In both cases, the formula was array entered as desired and returned the
correct answer.

I believe this has been true at least since xl97.

 

[Dave Peterson]

And this is a good reason to always test what you read in xl's help <vbg>.

I think that this remark has been wrong in all versions (xl97 to xl2003 for
sure):

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).