Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2 | Excel Discussion (Misc queries) | |||
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2 | Excel Worksheet Functions | |||
can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
R1C1 reference style | Excel Discussion (Misc queries) | |||
Range.FormulaArray drops relative reference ? | Excel Programming |