"Unable to set the FormulaArray property..."
I hope someone can help me with this. When I manually enter this array formula into a cell using Ctrl-Shift-Enter, everything works fine:
=IF(ISERROR(INDEX('Oct 05 FINAL'!$J$27:$J$1500,MATCH(1,('Oct 05 FINAL'!$C$27:$C$1500=$B4)*('Oct 05 FINAL'!$E$27:$E$1500=$D4),0))),"",INDEX('Oct 05 FINAL'!$J$27:$J$1500,MATCH(1,('Oct 05 FINAL'!$C$27:$C$1500=$B4)*('Oct 05 FINAL'!$E$27:$E$1500=$D4),0)))
But, when I run this same event with code, the following code:
Selection.FormulaArray = "=IF(ISERROR(INDEX('Oct 05 FINAL'!$J$27:$J$1500,MATCH(1,('Oct 05 FINAL'!$C$27:$C$1500=$B4)*('Oct 05 FINAL'!$E$27:$E$1500=$D4),0))),"""",INDEX('Oct 05 FINAL'!$J$27:$J$1500,MATCH(1,('Oct 05 FINAL'!$C$27:$C$1500=$B4)*('Oct 05 FINAL'!$E$27:$E$1500=$D4),0)))"
...DOES NOT WORK. I get an error msg: "Unable to set the FormulaArray property of the Range class"
If I leave off the "IF(ISERROR(..." in my code and run it, it works OK, but not if I include it. I need to have ISERROR so my cells aren't populated with "#VALUE!" or any other error indicators when the values aren't found.
Anybody have the answer?
Thanks,
Randy
|