VBA: How to place array formula in a cell?

[Egad]

To manually place an array formula (like the one below) into a cell I use CTRL-SHIFT-ENTER.
=sum(if(b1:B100="Yes",c1:c100*d1:d100))

How would I do it thru vba? It doesn't seem to work like this:
cells(1,1).value=...formula...

 

[Dave Patton]

On 2002-12-11 14:53, Egad wrote:
To manually place an array formula (like the one below) into a cell I use CTRL-SHIFT-ENTER.
=sum(if(b1:B100="Yes",c1:c100*d1:d100))

How would I do it thru vba? It doesn't seem to work like this:
cells(1,1).value=...formula...

I recorded an array formula with Recorder on
and it showed

Selection.FormulaArray = _
"=SUM(IF((R5C2:R103C2=RC[-6])*(R5C10:R103C10=RC[-5]),(R5C4:R103C6)))"

I would check .FormulaArray=

 

[Mudface]

Have a look at the FormulaArray property in VBA help. It's probably easier if you record yourself entering array formulae a few times, as it requires the use of R1C1 notation. In your case, placing the formula in cell E1 yields the following code: -
<pre>
Range("E1").FormulaArray = _
"=SUM(IF(RC[-3]:R[99]C[-3]=""Yes"",RC[-2]:R[99]C[-2]*RC[-1]:R[99]C[-1]))"
</pre>

 

[Paul B]

try this
Selection.FormulaArray = _
"=sum(if(b1:B100=""Yes"",c1:c100*d1:d100))"

 

[Mudface]

On 2002-12-11 15:00, Paul B wrote:
try this
Selection.FormulaArray = _
"=sum(if(b1:B100=""Yes"",c1:c100*d1:d100))"

, and there's me checking the helpfile which says you must use R1C1 notation. Don't believe everything you read in there...

 

[Egad]

Thank you all.
.FormulaArray it is.
;o)

 

[pmpatane]

To make it a little interactive, try:
UserResponse = Chr$(34) & InputBox("Yes or No?") & Chr$(34)
Selection.FormulaArray = "=sum(if(b1:B100=" & UserResponse & ",c1:c100*d1:d100))"

 

[damned]

To make it a little interactive, try:
UserResponse = Chr$(34) & InputBox("Yes or No?") & Chr$(34)
Selection.FormulaArray = "=sum(if(b1:B100=" & UserResponse & ",c1:c100*d1:d100))"

Haha sound advice but I think you were out by about 7 years in this thread lol good try though!!

 

[julia55]

I am trying to enter an array formula in VBA but I have to use the R1C1 reference but it is not working. When I copy the formula over to another cell it changes all of my row and columns. Example is below.

Original formula:
{=SUM(IF('Timing Review'!C2=RC1,IF('Timing Review'!C11="Same Day",1,0),0))}

When I copy it through VBA the formula is becoming:
{=SUM(IF('Timing Review'!R[-2]C[-20]=R[-3]C[448],IF('Timing Review'!R[7]C[-20]="Same Day",1,0),0))}

Any suggestions?

 

[AliCat]

I googled this and it came up first hit. Well, 12 years on and it is still relevant.