FormulaArray..... HELP !!!
 

Basicely, here is my problem : try to run this sub in a new sheet :

Sub test()
Range("D10:N10").Select
Selection.FormulaArray =
=fpfp(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a
14,a15,a16,a17,a18,a19,a20,a21,a22)"
End Sub


As the function fpfp is not defined, it should return a #NAME.

Now, add another argument at the end of the function call : a23
The string is not very long : for sure less than 100 chars.

Then you should obtain an msgbox runtime error 1004 ("Unable to set th
FormulaArray property of the Range class")

So my simple question is : How can I do it ?????
:confused: :confused: :confused

--
Message posted from http://www.ExcelForum.com

FormulaArray..... HELP !!!
 

see response in previous thread:

Its about 256 characters in R1C1 mode, which is what it uses internally.

Even Excel2003 still seems to have this limit.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Pierre " wrote in message
...
Basicely, here is my problem : try to run this sub in a new sheet :

Sub test()
Range("D10:N10").Select
Selection.FormulaArray = "
=fpfp(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a
14,a15,a16,a17,a18,a19,a20,a21,a22)"
End Sub


As the function fpfp is not defined, it should return a #NAME.

Now, add another argument at the end of the function call : a23
The string is not very long : for sure less than 100 chars.

Then you should obtain an msgbox runtime error 1004 ("Unable to set the
FormulaArray property of the Range class")

So my simple question is : How can I do it ?????
:confused: :confused: :confused:


---
Message posted from http://www.ExcelForum.com/

FormulaArray..... HELP !!!
 

Thanks a lot William.

So basicely, I am stuck !!!

#?!£$%# Excel !!!
It is very annoying !

They should focus on this kind of problem instead of adding some ne
fancy functionalities !!!!:mad: :mad

--
Message posted from http://www.ExcelForum.com

FormulaArray..... HELP !!!
 

So basically, I am stuck !!!

I really don't understand it, but I remember "something" about this. Can't
find it in my notes. Here is just an idea. (General idea, and not A1:A24)

Basically, you need...

Sub Test()
Range("D10:N10").FormulaArray = "=fpfp($a$1,$a$2,$a$3,$a$4,...$a$23,$a$24)"
End Sub


Sub Bad()
Dim rng
With Range("D10:N10")
rng = [a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14,
a15, a16, a17, a18, a19, a20, a21, a22, a23, a24].Address(False, False)
.FormulaArray = Replace("=fpfp(#)", "#", rng)
End With
End Sub


Sub Good()
Dim rng
With Range("D10:N10")
rng = [a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14,
a15, a16, a17, a18, a19, a20, a21, a22, a23, a24].Address
.FormulaArray = Replace("=fpfp(#)", "#", rng)
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Pierre " wrote in message
...
Thanks a lot William.

So basicely, I am stuck !!!

#?!£$%# Excel !!!
It is very annoying !

They should focus on this kind of problem instead of adding some new
fancy functionalities !!!!:mad: :mad:


---
Message posted from http://www.ExcelForum.com/

FormulaArray..... HELP !!!
 

Like Dana, I seem to remember a bypass existed to the max 256 character
formula problem but I cant find it ...

some ideas that may help:
- use short names for the ranges.
- use absolute rather than relative because its shorter in R1C1 mode.
- use short sheet names (rename afterwards if required)


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Pierre " wrote in message
...
Thanks a lot William.

So basicely, I am stuck !!!

#?!£$%# Excel !!!
It is very annoying !

They should focus on this kind of problem instead of adding some new
fancy functionalities !!!!:mad: :mad:


---
Message posted from http://www.ExcelForum.com/