Unable to set the FormulaArray property of the Range Class

[Nickp2]

Hello,

I'm trying to use a FormulaArray and after splitting the formula I'm still getting the same runtime error "1004: Unable to set the FormulaArray property of the Range Class". I used the code from Entering Long Array Formulas in VBA – Daily Dose of Excel for testing which worked but then when putting in my own formula I get the error. Any help would be appreciated.

Nick

 

[Nickp2]

theFormulaPart1 = "=INT(IF(BM4="""","""",(BM4/VLOOKUP(H4&C4,S!$I$2:$O$9999,7,0))*" & _
"VLOOKUP(H4&C4,S!$I$2:$Q$9999,6,0)))+"

theFormulaPart2 = "X_X_X())" & _
"LOOKUP(MOD(IF(BM4="""","""",(BM4/VLOOKUP(H4&C4,S!$I$2:$O$9999,7,0))*" & _
"VLOOKUP(H4&C4,S!$I$2:$Q$9999,6,0)),1),{0,0.3,0.5,0.8},{0.29,0.49,0.79,0.99})"

With ActiveSheet.Range("A2:A7")
.FormulaArray = theFormulaPart2
.Replace "X_X_X())", theFormulaPart1
'.NumberFormat = "mmm dd"
End With

 

[Nickp2]

theFormulaPart1 = "=INT(IF(BM4="""","""",(BM4/VLOOKUP(H4&C4,S!$I$2:$O$9999,7,0))*" & _
"VLOOKUP(H4&C4,S!$I$2:$Q$9999,6,0)))+"

theFormulaPart2 = "X_X_X())" & _
"LOOKUP(MOD(IF(BM4="""","""",(BM4/VLOOKUP(H4&C4,S!$I$2:$O$9999,7,0))*" & _
"VLOOKUP(H4&C4,S!$I$2:$Q$9999,6,0)),1),{0,0.3,0.5,0.8},{0.29,0.49,0.79,0.99})"

With ActiveSheet.Range("A2:A7")
.FormulaArray = theFormulaPart2
.Replace "X_X_X())", theFormulaPart1
'.NumberFormat = "mmm dd"
End With

My guess is that because theres no array in the first portion it gives the error. Adding the replace variable to the beginning of the second portion and then replacing it with the first portion worked. n case someone else gets stuck in a similar situation