method FormulaArray of object range failed Error Help

Why does the below code work fine and the other doesn't

Please Login or Register  to view this content.

But the below code doesn't work it gives me the following error message "method FormulaArray of object range failed" it stops at the last line of code below

Please Login or Register  to view this content.

As seen on my attached file Project file works perfectly fine but in Project2 file when I add in the "Const ApplyRngAddress As String = "A4" and "Set ChangedCll = Intersect(Target, Range(ApplyRngAddress))" code line it doesn't work.

The contents of the variables FcstFormula and ActualFormula are not being added to the formula, instead the text is.

Untested but the code should read more like this.

Please Login or Register  to view this content.

Also the last element should be ActualFormula rather than just Actual as you had.

I use Fcst and Actual as place holders because after that line of code Im using replace to change Fcst and Actual to the actual formula since the formula itself is to long for FormulaArray. Also if that’s the case why does it run without no error message in my top code?

You need to post an example file so we can actual test.

See yellow banner at top of page on how to attach example file.

It would be useful to have a cell with the required formula, that works, for comparison with your vba generated formula.

I've added the file to the forum. As you can see the VBA works perfectly fine in the Project file but in Project 2 file it give me the error message.

Glad you were able to resolve your own problem, and thanks for posting the cure.