3 tips to make the macro experience possibly better - KING OF EXCEL

Tuesday, December 31, 2019

3 tips to make the macro experience possibly better

3 tips to make the macro experience possibly better

Tip 1: Turn on Relative Reference for Every Macro.

Say that you start in A2 and record a simple macro that moves to A4. I would call that macro MoveDownTwoCells. But if you run this macro while the cell pointer is in J10, the macro will move to cell A4. This is rarely what you want to have happen. But you can change the behavior of the macro recorder by selecting View, Macros, Use Relative References before you record the macro.

There is a Macros drop-down on the View tab. The third choice is called Use Relative References.

If you record the MoveDownTwoCells macro with this setting turned on, the macro will work from any cell.
The setting stays turned on only until you close Excel. Tomorrow, you will have to turn it back on again.
What if you actually need to record a macro that always jumps to cell A4? Even with Relative References enabled, you could press F5 for Go To and use the Go To dialog to go to A4. The macro recorder will record code that will always jump to A4.

Tip 2: Use Ctrl+Shift+Arrow to move to the end of a data set.

Say that you want to add a total at the bottom of yesterday‘s invoice register. You want the total to appear in row 9 today, but tomorrow, it might need to be in row 17 or row 5, depending on how many rows of data you have.
Find a column that is reliably 100% filled. From the top of that column, record the action of pressing Ctrl+Shift+Down Arrow. Press the Down Arrow key one more time, and you will know you are in the row where the totals should be.

If this is a CSV file, you will always be in cell A1 when it is open. Press Ctrl+Shift+Down Arrow in the macro will get you to the last row with data today.

Tip 3: Type =SUM(E$2:E8) instead of pressing the AutoSum button

The macro recorder will not record the intent of AutoSum. When you press AutoSum, you will get a sum function that starts in the cell above and extends up to the first non-numeric cell. It does not matter if you have Relative References on or off; the macro recorder will hard-code that you want to sum the seven cells above the active cell.
Instead of using the AutoSum icon, type a SUM function with a single dollar sign before the first row number: =SUM(E$2:E8). Use that formula while recording the macro, and the macro will reliably sum from the cell above the active cell all the way up to row 2, as shown below.

With numbers in E2:E8, you would normally use a formula of =SUM(E2:E8). However, if you record the macro to use =SUM(E$2:E8), the formula will work for any size data set.
#evba #etipfree #kingexcel

📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment

Popular Posts