FormulaArray problem

[Bri]

Hi all

I've been having trouble using code to write an array formula to a cell. I
get the msg "Unable to set the FormulaArray property of the Range class"
after the following code:

str1 = "ugly formula goes here ..... 300+ characters later"
ActiveSheet.Range("H8").FormulaArray = str1


Now, if I manually enter the array formula in cell H8, it works well.
If I use .Formula instead of .FormulaArray, it also works after I use
<ctrl><shift><enter>. I suspect their may be a limit on formula size when
using .FormulaArray, but I can't find this in the excel help. (actually,
there's lots I can't find here)

Is there a way to work around this apparent limiltation in FormulaArray?

Thanks
Bri

 

[damorrison]

Are formulas allowed to be more than 256 charactors long??

and
Wow!... you need a formula over 300 charactors long, what's it doing

 

[Dave Peterson]

Maybe this workaround from Dick Kusleika's site would work:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Or

http://snipurl.com/pl1q

 

[Bri]

Thanks Dave - works like a charm!

interesting how REPLACE can be used to work around this 255 character
limitation.
Bri