unable to set the formulaarray property of the range class - IF AND

[zk69]

Hello


I got an error message (... error 1004 - unable to set the formulaarray property of the range class) when I try to use the following VBA code.


I defined three constants:


Const Formula1_Valid = "=IF(AND(D38<E38; D39<E39; D40<E40); ""VALID""; ""NOTVALID"")"
Const Formula2_Valid = "=IF(AND(D38<E38; D39<E39; D40<E40; F38<G38; F39<G39; F40<G40); ""VALID""; ""NOTVALID"")"
Const Formula3_Valid = "=IF(AND(D38<E38; D39<E39; D40<E40; F38<G38; F39<G39; F40<G40; H38<I38; H39<I39; H40<I40); ""VALID""; ""NOTVALID"")"


When a certain event occurs (If Target.Address = "$G$5" Then...) I would like to put a formula into a cell E45.


I try to use the following instruction: Sheet1.Range("E45").FormulaArray= Formula1_Valid


What is the problem with this code?
It works if I use the following constant: =SUM(F38:G38)
The cell E45 hasn"t been merged. The formula doesn't exceed 255 Characters.

[mancubus]

i dont think you need an array formula.
instead try FormulaLocal property (since argument separator is ";") or Formula property by replacing semicolons with commas.

[zk69]

Thank you so much for your help. I really appreciate it.
FormulaLocal is perfect.