Range.FormulaArray drops relative reference ?
 

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

Range.FormulaArray drops relative reference ?
 

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

--
Regards,
Tom Ogilvy

"SPaquin" wrote in message
...
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

Range.FormulaArray drops relative reference ?
 

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.

Range.FormulaArray drops relative reference ?
 

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

"Yan-Hong Huang[MSFT]" wrote:

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.

Range.FormulaArray drops relative reference ?
 

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.