how to write a macro to press ctrl+shift+enter for us?

[Apocolyse]

Hi all, I just want to know that if there is a way to make a VBA that can change normal formula to array formula by: select, edit , press ctrl + shift + enter? (I already wrote all the array formula without pressing ctrl + shift + enter for dragging purpose, now I want to turn those into real array formula). Looking forward to hear from u guys ^^

 

[hiker95]

Apocolyse,

There is a way to write the array formulae in VBA without using CTRL + SHIFT + ENTER

Can we see some of the macro code you are now using to write the formula to a cell or range of cells?

If posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]

 

[hiker95]

Apocolyse,

If we start with this:

Excel 2007
	A	B	C	D	E
1	1	2	3
2	4	5	6
3	7	8	9
4
Sheet1

And we run the following code:

Sub Test()

Worksheets("Sheet1").Range("E1").FormulaArray = "=Sum(R1C1:R3C3)"

End Sub

We get this:

Excel 2007
	A	B	C	D	E
1	1	2	3		45
2	4	5	6
3	7	8	9
4
Sheet1

Cell Formulas
Range	Formula
E1	{=SUM($A$1:$C$3)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Apocolyse]

ah , seem like my explanation is not sufficient ^^. Like in your example, I want to enter formula array at: E1 = SUM($A$1:$C$1), E2 = SUM($A$2:$C$2), E3 = SUM($A$3:$C$3). If I enter the formula at E1 as array formula, I can't drag it down to E2, E3 (the range inside the formula won't automatically change) => so I need to repeatly enter the formula (my datasheet has more than 5000 lines, I can't do that ^^). So instead, 1st I enter it like a normal formula, then drag down to get the range change automatically, then must use VBA to convert those formula back to array somehow. That's wat I need ^^. And btw, idk why I can't press enter in the reply box here in 4rum o.o

 

[hiker95]

Apocolyse,

Sample raw data:

Excel 2007
	A	B	C	D	E
1	1	2	3
2	4	5	6
3	7	8	9
4	10	11	12
5	13	14	15
6	16	16	18
7
Sheet1

After the latest macro:

Excel 2007
	A	B	C	D	E
1	1	2	3		6
2	4	5	6		15
3	7	8	9		24
4	10	11	12		33
5	13	14	15		42
6	16	16	18		50
7
Sheet1

Cell Formulas
Range	Formula
E1	{=SUM(A1:C1)}
E2	{=SUM(A2:C2)}
E3	{=SUM(A3:C3)}
E4	{=SUM(A4:C4)}
E5	{=SUM(A5:C5)}
E6	{=SUM(A6:C6)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub TestV2()
' hiker95, 03/30/2013
' http://www.mrexcel.com/forum/excel-questions/694333-how-write-macro-press-ctrl-shift-enter-us.html
Dim lastrow As Long
With Worksheets("Sheet1")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  .Range("E1").FormulaArray = "=SUM(A1:C1)"
  .Range("E1").AutoFill Destination:=.Range("E1:E" & lastrow)
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the TestV2 macro.

 

[Rick Rothstein]

ah , seem like my explanation is not sufficient ^^. Like in your example, I want to enter formula array at: E1 = SUM($A$1:$C$1), E2 = SUM($A$2:$C$2), E3 = SUM($A$3:$C$3). If I enter the formula at E1 as array formula, I can't drag it down to E2, E3 (the range inside the formula won't automatically change) => so I need to repeatly enter the formula (my datasheet has more than 5000 lines, I can't do that ^^). So instead, 1st I enter it like a normal formula, then drag down to get the range change automatically, then must use VBA to convert those formula back to array somehow. That's wat I need ^^. And btw, idk why I can't press enter in the reply box here in 4rum o.o

If you want to copy the formula down from E1 to E2, E3, etc., then why have you made your addresses absolute? Just make them relative (no $ signs) and they will copy down exactly like you are indicating you want.