Hi
I am trying to apply a formula to the FormulaArray property of a range in
VBA code and I have trouble with the cell references are not updated as is
usually the case. Here is an example. It has been heavily simplified to
emphasize the problem but I need to put a complex array formula in hundreds
of rows. The following example could be used to get the sum of values in
NamedRange greater than the value in the left column.

The following VBA code works correctly:

Names.Add Name:="NamedRange" _
, RefersToR1C1:=ActiveSheet.Range(SomeRange)

ActiveSheet.Cells(2,2).FormulaArray = _
"=SUM(IF(NamedRangeRC[-1],NamedRange))"
ActiveSheet.Cells(3,2).FormulaArray = _
"=SUM(IF(NamedRangeRC[-1],NamedRange))"

Resulting formulas
B2 : =SUM(IF(NamedRangeA2,NamedRange))
B3 : =SUM(IF(NamedRangeA3,NamedRange))

The following does not work.
ActiveSheet.Range(Cells(2,2),Cells(3,2)).FormulaAr ray = _
"=SUM(IF(NamedRangeRC[-1],NamedRange))"

The resulting formula in B2 and B3 is exactly the same.
B2 : =SUM(IF(NamedRangeA2,NamedRange))
B3 : =SUM(IF(NamedRangeA2,NamedRange)) ' <Wrong reference

I have lost the relative reference to the left column in the second
formula(A3 is not there). Now, how do I get the relative reference to work
correctly ? Do I have to loop through all the cells to insert a correct
FormulaArray ? Is there a magic trick ?

TIA
Stephane
Excel 2002