Running the Macro on Another Day Produces Undesired Results

So, you would have saved your macro file. The next day, you come to work and you have a new invoice.txt file from the system. You open the macro, press Ctrl+i to run it, and disaster strikes. The data for June 5th happened to have 12 invoices. The data for the 6th had 16 invoices. However, the recorded macro blindly added the totals in Row 14, because this was where we put the totals when the macro was recorded (see Figure 1.13).

Figure 1.13

On another day,the macro fails horribly.The intent of the recorded macro was to add a total at the end of the data, but the recorder made a macro to always add totals at Row 14.

A Possible Solution: Using Relative References when Recording

By default, the macro recorder records all actions as absolute actions. If you navigate to Row 14 when you record the macro on Monday, then the macro will always go to Row 14 when the macro is run. When dealing with variable numbers of rows of data, this is rarely appropriate.

There is an option to use relative references when recording.

Absolute reference means that the actual address of the cell is used, for example "A1." Relative reference means that the location of the cell is based off of another cell. For example, R[16],C[-1] refers to a cell 16 rows down and 1 column to the left of the active cell.

0 0

Post a comment