Ctrl+Shift+Enter for arrays

[chris100]

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

 

[Guest]

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

 

[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

 

[Kris]

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

 

[Guest]

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