FormulaArray alternative for A1 reference style?
 

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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

FormulaArray alternative for A1 reference style?
 

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

KR wrote:
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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

FormulaArray alternative for A1 reference style?
 

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.

--
Regards,
Tom Ogilvy


"KR" wrote:

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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

FormulaArray alternative for A1 reference style?
 

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


KR wrote:

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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

--

Dave Peterson