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

Apocolyse

Board Regular
Joined
Dec 26, 2012
Messages
55
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 ^^
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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]
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Apocolyse,

If we start with this:


Excel 2007
ABCDE
1123
2456
3789
4
Sheet1



And we run the following code:

Rich (BB code):
Sub Test()

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

End Sub





We get this:


Excel 2007
ABCDE
112345
2456
3789
4
Sheet1
Cell Formulas
RangeFormula
E1{=SUM($A$1:$C$3)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Apocolyse

Board Regular
Joined
Dec 26, 2012
Messages
55
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Apocolyse,

Sample raw data:


Excel 2007
ABCDE
1123
2456
3789
4101112
5131415
6161618
7
Sheet1


After the latest macro:


Excel 2007
ABCDE
11236
245615
378924
410111233
513141542
616161850
7
Sheet1
Cell Formulas
RangeFormula
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).

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,944
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,748
Messages
5,855,442
Members
431,733
Latest member
bestassignmentexpert789

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top