Range.FormulaArray drops relative reference ?

[SPaquin]

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(NamedRange>RC[-1],NamedRange))"
ActiveSheet.Cells(3,2).FormulaArray = _
"=SUM(IF(NamedRange>RC[-1],NamedRange))"

Resulting formulas
B2 : =SUM(IF(NamedRange>A2,NamedRange))
B3 : =SUM(IF(NamedRange>A3,NamedRange))

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

The resulting formula in B2 and B3 is exactly the same.
B2 : =SUM(IF(NamedRange>A2,NamedRange))
B3 : =SUM(IF(NamedRange>A2,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

 

[Tom Ogilvy]

Yes. You can have single cell formula arrays (what you want) or multiple
cell formula arrays (what you are getting). So you will need to loop and
apply each cell individually

 

[Yan-Hong Huang[MSFT]]

Hello Stephane,

I am reviewing the issue thread. Tom has given suggestion on it. Have you
successfully resovled the problem? If not, please feel free to reply here
and we are glad to work with you on it.

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
Register to Access MSDN Managed Newsgroups!
-http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
p&SD=msdn

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[SPaquin]

Even thouh the solution is not what I wanted the program to do, yes, Tom has
answered my question.
Stephane

 

[Peter Huang [MSFT]]

Hi Stephane,

Thanks for your resposne!
If you still have any concern on this issue, please feel free to post here.




Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.