|
Subject:
|
"Unable to set the FormulaArray property..."
|
|
Posted By:
|
rduncan1
|
Post Date:
|
8/17/2006 10:37:59 AM
|
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
|
|
Reply By:
|
jrogers
|
Reply Date:
|
8/18/2006 5:48:34 AM
|
unfortunatly it may be due to this http://support.microsoft.com/kb/213181/EN-US/
|
|
Reply By:
|
rduncan1
|
Reply Date:
|
8/18/2006 1:33:20 PM
|
Thanks, jrogers! That's the problem. I'll have to figure a way to get the data another way or use no more than 255 characters!
|
|
