Ctrl+Shift+Enter for arrays
 

Hi all,

I have lots (and i do mean lots) of formulas that i need to change t
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 d
them individually!!

Thanks in advance,

Chri

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=50503

Ctrl+Shift+Enter for arrays
 

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

Ctrl+Shift+Enter for arrays
 

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" wrote in
message ...
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

Ctrl+Shift+Enter for arrays
 

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

Ctrl+Shift+Enter for arrays
 

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

Ctrl+Shift+Enter for arrays
 

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