Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 17th, 2006, 10:37 AM
Registered User
 
Join Date: Jul 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default "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
 
Old August 18th, 2006, 05:48 AM
Authorized User
 
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

unfortunatly it may be due to this http://support.microsoft.com/kb/213181/EN-US/

 
Old August 18th, 2006, 01:33 PM
Registered User
 
Join Date: Jul 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable so assign value to .Lookin Property Colster Excel VBA 1 August 24th, 2007 03:16 PM
Get Set Property value being lost asn187 ASP.NET 2.0 Professional 1 April 19th, 2007 03:09 PM
Set Listbox Selected property DaDeViL VB How-To 7 October 4th, 2006 04:04 PM
Set accountExpires property r_ganesh76 C# 0 August 9th, 2006 01:16 AM
Unable to set image or cursor property, Ch 12. VictorVictor BOOK: Beginning CSS: Cascading Style Sheets for Web Design ISBN: 978-0-7645-7642-3 5 April 26th, 2006 10:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.