ArrayFormula as Value in VBA

[mucah!t]

Hi all,

How do I get the following code to insert the result rather than the formula itself in a cell

Range("AH2").FormulaArray = "=SUM(IF(WERKBLAD!R[2]C[-30]:R[2]C[222]=RC[-2],IF(WERKBLAD!R[4]C[-32]:R[998]C[-32]>=RC[-4],IF(WERKBLAD!R[4]C[-32]:R[998]C[-32]<=RC[-3],IF(WERKBLAD!R[4]C[-30]:R[998]C[222]=""Z"",1)))))"

 

[RoryA]

You could try:

With Range("AH2")
   .FormulaR1C1 = "=SUMPRODUCT(--(WERKBLAD!R[2]C[-30]:R[2]C[222]=RC[-2]),--(WERKBLAD!R[4]C[-32]:R[998]C[-32]>=RC[-4]),--(WERKBLAD!R[4]C[-32]:R[998]C[-32]<=RC[-3]),--(WERKBLAD!R[4]C[-30]:R[998]C[222]=""Z""))"
   .Value = .Value
End With

 

[mucah!t]

Hello Rorya,

Thanks for trying, but it results in a #value error

 

[RoryA]

I didn't look at your formula closely enough - the ranges are different shapes. Try this:

With Range("AH2")
   .FormulaR1C1 = "=SUMPRODUCT((WERKBLAD!R[2]C[-30]:R[2]C[222]=RC[-2])*(WERKBLAD!R[4]C[-32]:R[998]C[-32]>=RC[-4])*(WERKBLAD!R[4]C[-32]:R[998]C[-32]<=RC[-3])*(WERKBLAD!R[4]C[-30]:R[998]C[222]=""Z""))"
   .Value = .Value
End With

 

[mucah!t]

Thanks Rorya, works great!