zk69
11-04-2016, 02:44 PM
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.
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.