ctrl/shift/enter thru VBA

  • Thread starter Thread starter Jason Morin
  • Start date Start date
J

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
 
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?
 
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
 
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
 
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 ????:( :( :
 
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)
 
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
 
Back
Top