Ctrl+Shift+Enter for arrays

[chris100]

Hi all,

I have lots (and i do mean lots) of formulas that i need to change to arrays using the CTrl+Shift+Enter method.

Is there a way i can do them as a group? Please don't say i have to do them individually!!

Thanks in advance,

Chris

[Jim Thomlinson]

Unless you can drag or copy the formula then (to the best of my knowledge)
you are stuck with Ctrl + Shift + Enter... It may be worthwile to rewrite the
array formulas as sum product formulas... Here is a link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"chris100" wrote:

>
> Hi all,
>
> I have lots (and i do mean lots) of formulas that i need to change to
> arrays using the CTrl+Shift+Enter method.
>
> Is there a way i can do them as a group? Please don't say i have to do
> them individually!!
>
> Thanks in advance,
>
> Chris
>
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=505031
>
>

[Ed]

I've just begun to look into using array formulas in VBA code, so I have
absolutely no experience with this. But would there be a way to put the
cell's formula into a string, and then use the ForumlaArray property to
reset it?

Ed

"Jim Thomlinson" <[email protected]> wrote in
message news:[email protected]...
> Unless you can drag or copy the formula then (to the best of my knowledge)
> you are stuck with Ctrl + Shift + Enter... It may be worthwile to rewrite
the
> array formulas as sum product formulas... Here is a link...
>
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> --
> HTH...
>
> Jim Thomlinson
>
>
> "chris100" wrote:
>
> >
> > Hi all,
> >
> > I have lots (and i do mean lots) of formulas that i need to change to
> > arrays using the CTrl+Shift+Enter method.
> >
> > Is there a way i can do them as a group? Please don't say i have to do
> > them individually!!
> >
> > Thanks in advance,
> >
> > Chris
> >
> >
> > --
> > chris100
> > ------------------------------------------------------------------------
> > chris100's Profile:
http://www.excelforum.com/member.php...o&userid=25166
> > View this thread:
http://www.excelforum.com/showthread...hreadid=505031
> >
> >

[Kris]

Ed wrote:
> I've just begun to look into using array formulas in VBA code, so I have
> absolutely no experience with this. But would there be a way to put the
> cell's formula into a string, and then use the ForumlaArray property to
> reset it?
>
> Ed
>


Yes, but you must know what you are doing.


range("a1:g7").formulaarray = range("a1").formula

[chris100]

I'm not to sure myself Ed, and I don't fancy having to rewrite using sum product.

What about using a procedure that when run, will go through a column of cells, Ctrl shifting and entering until a blank is found? Unfortunately I'm still pretty amateurish with looping and procedures...so anyone have any ideas?

regards,

chris

[Jim Thomlinson]

This should be a start for you. It converts all of the formulas in Column A
to array formulas. You just need to change Sheet1, A1 and A...

Sub MakeArray()
Dim wks As Worksheet
Dim rngToConvert As Range
Dim rngCurrent As Range

Set wks = Sheets("Sheet1")
With wks
Set rngToConvert = .Range(.Range("A1"), .Cells(Rows.Count,
"A").End(xlUp))
End With
For Each rngCurrent In rngToConvert
rngCurrent.FormulaArray = rngCurrent.Formula
Next rngCurrent
End Sub

--
HTH...

Jim Thomlinson


"chris100" wrote:

>
> I'm not to sure myself Ed, and I don't fancy having to rewrite using sum
> product.
>
> What about using a procedure that when run, will go through a column of
> cells, Ctrl shifting and entering until a blank is found? Unfortunately
> I'm still pretty amateurish with looping and procedures...so anyone
> have any ideas?
>
> regards,
>
> chris
>
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=505031
>
>