RunTime Error '1004' - unable to set the formulaarray property of the range class

[jwoo89]

Hi!

I have put the below formula into excel and it worked.

=IF(AND(B4="occ",H4="long"),K4,IF(AND(B4="occpm",H4="long"),K4*-1,IF(AND(B4="occps",H4="long"),K4*-1,IF(AND(B4="margin",H4="long"),K4,IF(AND(B4="occ",H4="short"),K4*-1,IF(AND(B4="occpm",H4="short"),K4,IF(AND(B4="occps",H4="short"),K4,IF(AND(B4="margin",H4="short"),K4*-1))))))))

But when i adjusted to the formulaarray in my vba with the below - i get that runtime error '1004' - Unable to set the FormulaArray property of the Range class


Range("I4").FormulaArray = "IF(AND($B4=""occ"",$H4=""long""),$K4,IF(AND($B4=""occpm"",$H4=""long""),$K4*-1,IF(AND($B4=""occps"",$H4=""long""),$K4*-1,IF(AND($B4=""margin"",$H4=""long""),$K4,IF(AND($B4=""occ"",$H4=""short""),$K4*-1,IF(AND($B4=""occpm"",$H4=""short""),$K4,IF(AND($B4=""occps"",$H4=""short""),$K4,IF(AND($B4=""margin""$,H4=""short""),$K4*-1))))))))"

Appreciate all the help!!

Best,
Justine

 

[StephenCrump]

This is not an array formula. Your code should work if you use:

Range("I4").Formula = ....

If it was an array formula, there is a 255 character limit: You can work around it like this: Entering Long Array Formulas in VBA – Daily Dose of Excel