FormulaArray problem
 

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

FormulaArray problem
 

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

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

FormulaArray problem
 

Maybe this workaround from Dick Kusleika's site would work:
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

Or

http://snipurl.com/pl1q

Bri wrote:

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

--

Dave Peterson

FormulaArray problem
 

Thanks Dave - works like a charm!

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

Peterson" wrote in message
...
Maybe this workaround from Dick Kusleika's site would work:
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

Or

http://snipurl.com/pl1q

Bri wrote:

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

--

Dave Peterson