1.4 A Macro for Simplifying Input

In inputting data in tabular form it frequently happens that the number or text to be input into a cell is exactly the same as what was input into the cell immediately above it. Excel offers a variety of possibilities for copying into the cell below, but all of them require either using the mouse (which is a nuisance when you are using the keyboard for data entry) or engaging in elaborate movement of the arrow cursors . It makes sense, then, to create a macro that can accomplish such copying with a simple keyboard combination (for example, Ctrl+K ).

Preparatory Tasks

Excel can distinguish in macro recording between absolute and relative movement between cells :

Absolute recording is the default mode. When you move the cell pointer during recording from B2 to D4, say, the resulting command is Range("D4").Select .
In relative mode, however, the command would be ActiveCell.Offset(2, 2).Range("A1").Select : Here ActiveCell.Offset(2, 2) denotes the cell two rows beneath and two columns to the right of the currently active cell, while Range("A1") refers to this new address.

The differences between these two variants become apparent when the macros are executed. In the first case cell D4 will always be involved, irrespective of where the cell pointer is located. In the second case the affected cell is to be chosen relative to the current cell.

Since Excel 97 there has been no menu command for switching between relative and absolute recording. Instead, one must click on the Relative Reference tool in the "stop recording" toolbar. This toolbar appears automatically when you begin recording a macro. If the tool appears as a pressed-in button, then relative recording is in effect, otherwise , absolute. The mode can be changed during the recording process. For the macro to be created in this section relative reference is necessary.

Figure 1-6: The button on the right toggles between absolute and relative recording

Recording the Macro

Before you begin recording the macro, prepare the table: Input some text into a cell, and then move the cell pointer into the cell immediately below.

Begin recording by executing ToolsMacroRecord New Macro, giving the macro the name CopyFromCellAbove . Then select as shortcut key Ctrl+K , and indicate that the macro is to be stored in the personal macro workbook. Finally, select relative recording mode if it has not already been selected. This mode is necessary for this macro because it should function for an arbitrary cell in the table (and always copy the cell above relative to the cell pointer).

While the recorder is running, execute the following keyboard entries and commands: Shift+ ‘ (this selects the current cell and the cell lying above it), EditFillDown (this copies to the cell below), and finally, ’ (to move the cell pointer to the next cell to the right, where the next input can be made). End the recording with ToolsMacroStop Recording.

The following Visual Basic macro can now be found in the personal macro workbook:

' shortcut.xls
Sub CopyFromCellAbove()
  ActiveCell.Offset(-1, 0).Range("A1:A2").Select
  ActiveCell.Activate
  Selection.FillDown
  ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

If you now try out the macro, you will see that while in principle it runs, the cell pointer ends up one cell too high, to the right of the cell above rather than the cell below. There seems to be a slight contradiction between the recorded commands and the resulting code (that is, the automatic macro recorder has not functioned flawlessly). You can get around this shortcoming by changing the first Offset value in the last line of the macro as follows :

ActiveCell.Offset(1, 1).Range("A1").Select

Note	If during recording you forgot to provide the keyboard shortcut `Ctrl+K` , you can always give an existing macro a keyboard shortcut. To do this execute in Excel (not in the development environment) ToolsMacroMacros, select the macro, and insert the shortcut with Options.

Flylib.com

Category list

Similar pages

1.4 A Macro for Simplifying Input