ctrl/shift/enter thru VBA

[Jason Morin]

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

 

[Tom Ogilvy]

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?

 

[Chip Pearson]

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]

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

 

[Pierre]

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 ???? :

 

[Bob Phillips]

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]

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

 

[Charles Williams]

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