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

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