ctrl/shift/enter thru VBA
 

Hello. I have copied over a range of cells with some very
long array formulas by replacing the "=" with "##",
copying and pasting the formulas in their new locations,
and then replacing the "##" with "=". However, I'm having
to go through each cell and press ctrl/shift/enter. When I
attempted to record a macro to automate this process of
pressing ctrl/shift/enter, I receive "unable to record."

Goal: Select a range of cells and run a macro to convert
the existing formulas to array formulas.

Thanks.
Jason

ctrl/shift/enter thru VBA
 

formulaarray is used to enter a formula as an array. However, I believe it
only works with a string that it 255 characters in length (max).

You say long formulas - do they exceed 255 characters?

Why was it necessary to use the ##. Was the copying not being allowed when
they remained as array formulas?

--
Regards,
Tom Ogilvy

"Jason Morin" wrote in message
...
Hello. I have copied over a range of cells with some very
long array formulas by replacing the "=" with "##",
copying and pasting the formulas in their new locations,
and then replacing the "##" with "=". However, I'm having
to go through each cell and press ctrl/shift/enter. When I
attempted to record a macro to automate this process of
pressing ctrl/shift/enter, I receive "unable to record."

Goal: Select a range of cells and run a macro to convert
the existing formulas to array formulas.

Thanks.
Jason

ctrl/shift/enter thru VBA
 

Jason,

Use the FormulaArray property. E.g.,

Dim Rng As Range
On Error Resume Next
For Each Rng In Selection.SpecialCells(xlCellTypeFormulas)
Rng.FormulaArray = Rng.Formula
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jason Morin" wrote in message
...
Hello. I have copied over a range of cells with some very
long array formulas by replacing the "=" with "##",
copying and pasting the formulas in their new locations,
and then replacing the "##" with "=". However, I'm having
to go through each cell and press ctrl/shift/enter. When I
attempted to record a macro to automate this process of
pressing ctrl/shift/enter, I receive "unable to record."

Goal: Select a range of cells and run a macro to convert
the existing formulas to array formulas.

Thanks.
Jason

ctrl/shift/enter thru VBA
 

Hi Tom. Yes, most of the formulas are over 255 char. But
rather than using the ##, I just copied the formulas as
you suggested and they work fine. I often copy formulas
from ws to ws or wb to wb, and I don't want to lose the
integrity. So I use the ## trick. In this case all I had
to do was copy and paste. Thanks. Thanks for your help,
too, Chip.
Jason

-----Original Message-----
formulaarray is used to enter a formula as an array.
However, I believe it
only works with a string that it 255 characters in length
(max).

You say long formulas - do they exceed 255 characters?

Why was it necessary to use the ##. Was the copying not
being allowed when
they remained as array formulas?

--
Regards,
Tom Ogilvy

"Jason Morin" wrote in message
...
Hello. I have copied over a range of cells with some
very
long array formulas by replacing the "=" with "##",
copying and pasting the formulas in their new locations,
and then replacing the "##" with "=". However, I'm
having
to go through each cell and press ctrl/shift/enter.
When I
attempted to record a macro to automate this process of
pressing ctrl/shift/enter, I receive "unable to record."

Goal: Select a range of cells and run a macro to convert
the existing formulas to array formulas.

Thanks.
Jason


.

ctrl/shift/enter thru VBA
 

Actually, I have a similar problem :
I want to create a string (it is a call to a function with 2
arguments) and it seems that the problem is not really the lenght o
the string but the number of arguments !!

eg ,
if you have a function like

Public Function Foo(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q
r, s, t, u, v, w, x, y, z)
Foo = a
End Function

try a
Sub test()
range("B2:E2").select
selection.FormulaArray = "=Foo(A1,A2,A3,A4,A5,A6,A7... til A19)
End Sub

It will return an error
but reduce the number of input and it will work !! (unde XP pro/Exce
2002)

Any trick to have more arguments ????:( :( :

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

ctrl/shift/enter thru VBA
 

Pass an array of values and then in the function loop through the array

Function myFunc(Params)

For i = LBound(myParams) To UBound(Params)
' handle Params (i)
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pierre " wrote in message
...
Actually, I have a similar problem :
I want to create a string (it is a call to a function with 25
arguments) and it seems that the problem is not really the lenght of
the string but the number of arguments !!

eg ,
if you have a function like

Public Function Foo(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q,
r, s, t, u, v, w, x, y, z)
Foo = a
End Function

try a
Sub test()
range("B2:E2").select
selection.FormulaArray = "=Foo(A1,A2,A3,A4,A5,A6,A7... til A19)
End Sub

It will return an error
but reduce the number of input and it will work !! (unde XP pro/Excel
2002)

Any trick to have more arguments ????:( :( :(


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

ctrl/shift/enter thru VBA
 

Tx Bob but my problem is not to generate the string (sorry for th
misunderstanding...)

Basicely, 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, a14,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 question is : How can I do it

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

ctrl/shift/enter thru VBA
 

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
...
Tx Bob but my problem is not to generate the string (sorry for the
misunderstanding...)

Basicely, 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, a14,a15,a16,a17,a18,a19,a2
0,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 question is : How can I do it.


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