Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using FormulaArray in VBA | Excel Programming | |||
Copy FormulaArray | Excel Programming | |||
FormulaArray | Excel Worksheet Functions | |||
FormulaArray..... HELP !!! | Excel Programming | |||
FormulaArray | Excel Programming |