And we return again with another article that will explain a little more about the use of Macros in Excel. In the previous edition, we gave you the guidelines so that you understand how to remove or unlock the VBA Macros password from Exel.
And this time, following this interesting topic, we are going to teach you to step by step how to use and make relative references in Excel when recording Macros.
Do not worry so much if you are not at an advanced level about the knowledge of Microsoft Exel, since we will explain what a Macro is, how to edit or modify it and what it is used for. Macros are nothing more than a series of instructions that sequentially execute an order. And it uses a special language called VBA which stands for Visual Basic for Applications.
Excel Macros are often used to perform repetitive operations and I know these are executed automatically. In the case that we work with Marcos’s recorder, we can activate an option that will allow us to work with relative references. Below we will explain in a very didactic way how to use and make relative references in Excel when recording Macros.
How to use and make relative references in Excel when recording Macros
We will explain it to you with a very basic example so that you can understand it if you are not so familiar with the Office application. As we have already said, when we record a macro using relative references we can automatically place data in any cell.
The example that we will use will be that of a table that contains the data on the first and last names of people separately and we want these names and surnames to be joined in a new column. So the first thing we must do is locate the cell where we want the actions to take place.
Then we go to the main menu and we are going to choose the Programmer tab and then we go to the Record Macro command. While there we make a clip and this action will generate the Record Macro dialog box.
Here we must enter certain data, such as the Name of the Macro, the Shortcut Key, where we must place with which another letter it will be combined to execute this Macro. We must also enter a brief description, with which we will define what this Macro will be used for from now on.
Relative references in Excel to record Macros
And to finish, we make a clip on the Accept option, once this is done we will now go to the Use relative references command. The clip in this command will turn on and all the actions that I execute from now on will use the relative references. Now I am going to go to the cell I chose to apply the relative reference.
I select the cell and then we make the right clip so that the options are displayed and we choose Insert. A small dialog box will appear and in it, I will select the option to insert a new column. And then we click on the OK option, this action will allow a new column to appear and we move one cell down in this column.
Here we will write a formula that joins the names and surnames of the people, remember that this is an example so that you have an idea of how to use the relative reference. The formula is = C2 & ” ” & D2 now if we press the Ctrl and then Enter keys, the combination of the first and last name will appear in the cell.
To save the formula in the other cells of the column, we press the Ctrl + C keys, then we move to the next column where the surnames are found. Then press the Ctrl key and without releasing we move to the end of this column. Now we move to the next cell in the column where we want the combined names to appear.
Now I press the Ctrl + Shift + up arrow keys at the same time and to finish I press the Enter key. And as you can see all the combined names will appear in this column, now to finish you must go back to the first cell where you started recording the actions and then you stop the Macro recording and make a clip in the Relative Reference command.