The solution will be much closer to working correctly.įigure 1.14 Getting ready to record a second try.Īs you start to record the macro, go through the process of opening the Invoice.txt file. Let's try the same case study again, this time using relative references.
MACRO RECORDER CODE
For example, if the cell pointer starts in cell A1, the code ActiveCell.Offset(16, 1).Select would move the cell pointer to B17, which is the cell 16 rows down and 1 column to the right. Macros recorded with relative references note that the cell pointer should move a certain number of rows and columns from its current position. Macros recorded with absolute references note the actual address of the cell pointer, such as A11. The better option is to use relative references when recording. This is rarely appropriate when dealing with variable numbers of rows of data. If you navigate to Row 11 when you record the macro on Monday, the macro will always go to Row 11 when the macro is run. Possible Solution: Use Relative References When Recordingīy default, the macro recorder records all actions as absolute actions. Instead of using the default state of the macro recorder, the next section discusses relative recording and how this might get you closer to a final solution. This problem arises because the macro recorder is recording all your actions in absolute mode by default.
The comment for the Keyboard Shortcut is there to remind you of the shortcut.įigure 1.13 The intent of the recorded macro was to add a total at the end of the data, but the recorder made a macro that always adds totals at Row 11. The macro recorder starts your macros with a few comments, using the description you entered in the Record Macro dialog. Notice that some lines start with an apostrophe-these are comments and are ignored by the program. In your VBA Project ( MacroToImportInvoices.xls), find the component Module1, right-click the module, and select View Code. Don't worry if it doesn't make sense yet. Let's look at the code you just recorded from the case study. Switch to the VB Editor by selecting Visual Basic from the Developer tab or pressing Alt+F11. After you have performed the final step, click the Stop button in the lower-left corner of the Excel window or click Stop Recording in the Developer tab. Recorded macros move fast, but there is nothing like watching the macro recorder play out your mistakes repeatedly.Ĭarefully, execute all the actions necessary to produce the report. If you accidentally move to Column F, type a value, clear the value, and then move back to E to enter the first total, the recorded macro blindly makes that same mistake day after day after day. For this reason, perform your steps in exact order without extraneous actions. The macro recorder is now recording your every move, but don't be nervous. Click OK when you are ready.įigure 1.10 Before recording your macro, complete the Record Macro dialog box. In the Description field, add a little descriptive text to tell what the macro is doing (see Figure 1.10). You might want an easy way to run this macro later, so enter the letter i in the Shortcut Key field. Make sure that the macros will be stored in This Workbook. Change this to something descriptive like ImportInvoice. In the Record Macro dialog, the default macro name is Macro1.
Click the Record Macro button on the Developer tab. Open a blank workbook and save it with a name such as MacroToImportInvoices.xlsm. From the Home tab, select Format, AutoFit Column Width.Īfter you have rehearsed these steps in your head, you are ready to record your first macro.Highlight the Total row and click the Bold icon on the Home tab to set the totals in bold.Highlight Row 1 and click the Bold icon on the Home tab to set the headings in bold.Click the AutoFill handle and drag it from Column E to Column G to copy the total formula to Columns F and G.Click the Autosum button and press Ctrl+Enter to add a total to the Product Revenue column while remaining in that cell.Press the right-arrow key four times to move to Column E of the total row.Press the down arrow one more time to move to the total row.Press the End key followed by the down arrow to move to the last row of data.
0 kommentar(er)
