VBA Array formula error

[xsaiyanx4]

Hi guys! I've been recently getting into coding VBA, and am still relatively new to it (about 2 weeks in). I'm currently trying to use the following formula in vba as an array formula, but I get an error message " Unable to set the FormulaArray property of the range class". I've tried breaking it up into multiple parts, as a workaround for the character limit, but I still get the same error. Could anyone help check my code?

Original code is as follows:

 Cells(i, 5).FormulaArray = "=SUM(IF(YEAR('Details - USD'!" & ValDate.Address & ")=YEAR(P2),IF(MONTH('Details - USD'!" & ValDate.Address & ")<month(p2),if('details -="" usd'!"="" &="" currsold.address="" "="" cells(i,="" 3).value="" """,'details="" soldamt.address="" ",0),0),if(year('details="" valdate.address="" ")="YEAR(O2),IF(MONTH('Details">=MONTH(O2),IF('Details - USD'!" & CurrSold.Address & "=""" & Cells(i, 3).Value & """,'Details - USD'!" & SoldAmt.Address & ",0)))))/1000000"

Partitioned Code is as follows:

Cells(3, 5).Select
For i = 3 To 5
formulapart1 = "=SUM(IF(""Part2"",""Part3"")/1000000"<month(p2),if('details -="" usd'!"="" &="" currsold.address="" "="" cells(i,="" 3).value="" """,'details="" soldamt.address="" ",0),0)"

FormulaPart2 = <month(p2),if('details -="" usd'!"="" &="" currsold.address="" "="" cells(i,="" 3).value="" """,'details="" soldamt.address="" ",0),0)"
"YEAR('Details - USD'!" & ValDate.Address & ")=YEAR(P2),IF(MONTH('Details - USD'!" & ValDate.Address & ")<month(p2),if('details -="" usd'!"="" &="" currsold.address="" "="" cells(i,="" 3).value="" """,'details="" soldamt.address="" ",0),0)"

formulapart3 = "IF(YEAR('Details - USD'!" & ValDate.Address & ")=YEAR(O2),IF(MONTH('Details - USD'!" & ValDate.Address & ")>=MONTH(O2),IF('Details - USD'!" & CurrSold.Address & "=""" & Cells(i, 3).Value & """,'Details - USD'!" & SoldAmt.Address & ",0))))"
With Cells(i, 5)
    .FormulaArray = formulapart1
    .Replace what:="""Part2""", replacement:=FormulaPart2, lookat:=xlPart
    .Replace what:="""Part3""", replacement:=formulapart3, lookat:=xlPart
    End With

From what I can tell, the code should be first inputting the formulapart1 into the cell, then replacing the "Part2" in the formula with FormulaPart2, then replacing Part3 with formulapart3. however, I'm currently just getting the same error (" Unable to set the FormulaArray property of the range class") at the line which sets FormulaArray = formulapart1.

Any help at all would be appreciated!</month(p2),if('details></month(p2),if('details></month(p2),if('details></month(p2),if('details>

 

[Domenic]

It looks like you may have an extra bracket at the end. Also, I would suggest using more unique names for your place holders. Maybe something like this...

FormulaPart1 = "YEAR('Details - USD'!" & ValDate.Address & ")=YEAR(P2)"
FormulaPart2 = "MONTH('Details - USD'!" & ValDate.Address & ")=MONTH(O2)"
FormulaPart3 = "'Details - USD'!" & CurrSold.Address & "=""" & Cells(i, 3).Value & """"

'
'
'

With Cells(i, 5)
    .FormulaArray = "=SUM(IF(X_X_X,IF(Y_Y_Y,IF(Z_Z_Z,'Details - USD'!" & SoldAmt.Address & ",0))))/1000000"
    .Replace what:="X_X_X", replacement:=FormulaPart1, lookat:=xlPart
    .Replace what:="Y_Y_Y", replacement:=FormulaPart2, lookat:=xlPart
    .Replace what:="Z_Z_Z", replacement:=FormulaPart3, lookat:=xlPart
End With

Hope this helps!

 

[Rick Rothstein]

 Cells(i, 5).FormulaArray = "=SUM(IF(YEAR('Details - USD'!" & ValDate.Address & ")=YEAR([B][COLOR="#FF0000"]P2[/COLOR][/B]),IF(MONTH('Details - USD'!" & ValDate.Address & ")<month(p2),if('details -="" usd'!"="" &="" currsold.address="" "="" cells(i,="" 3).value="" """,'details="" soldamt.address="" ",0),0),if(year('details="" valdate.address="" ")="YEAR([B][COLOR="#FF0000"]O2[/COLOR][/B]),IF(MONTH('Details">=MONTH(O2),IF('Details - USD'!" & CurrSold.Address & "=""" & Cells(i, 3).Value & """,'Details - USD'!" & SoldAmt.Address & ",0)))))/1000000"

From the Excel Help Files for the FormulaArray property...

If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style

 

[Domenic]

From the Excel Help Files for the FormulaArray property...

Rick, yeah I know the Excel Help File says that the R1C1 reference style must be used, but the A1 style seems to work too. Have you had any problems using the A1 style?