How to change your Excel columns from letters to numbers and numbers to letters. For more guides, check out our website at www.techygeekshome.co.uk.

It's Friday, and your brain is almost full, but let's try to cram a little bit of Excel VBA in there before the weekend. We've talked about the Excel Column Headings before, and seen how to change the Reference Style setting from R1C1 (Numbers) to A1 (Letters).

That setting is pretty well hidden, and it's a bit of a pain to switch on and off. Epson tm-t82 driver for mac. We'll create a macro that will let us quickly switch from A1 to R1C1, or from R1C1 to A1.

Step 1: Record a Macro to Switch Column Headings to Numbers

To see the Excel VBA code that changes the setting, we can use the Excel Macro Recorder.

1. Turn on the Macro Recorder
2. Name the macro, ToggleR1C1, and store it in the Personal Macro Workbook
3. With the Macro Recorder on, turn the R1C1 reference style on, then turn it off
4. Turn off the Macro Recorder

Step 2: View the Recorded Excel VBA code

1. On Developer tab, click Macros
2. In the list of Macros, click ToggleR1C1
3. Click Edit, to see the recorded code, that will look something like this:
   
4. The lines that start with an apostrophe are comments, and you can delete them in this example.

Step 3: Edit the Recorded Excel VBA code

In the recorded Excel VBA code, you can see the two lines that change the reference style. The first line sets it to R1C1 and the second line sets it to A1 style. We'd like our macro to toggle the setting.

If it's currently A1, our macro will change it to R1C1, and vice versa. To do that, we'll add bit of If..Then code, similar to an IF formula on the worksheet.

Our code, if written in English instead of Excel VBA, would say this: If the current reference style is A1, change it to R1C1, otherwise (if it's not A1), change it to A1.

Here's how you can edit your code to say the same thing in Excel VBA:

The Macro Recorder showed us how to change the reference style setting. By adding If..Then..Else..End If, we created a simple macro that will make it easy to switch between the settings.

Step 4: Add the Macro to the QAT

To make this macro easy to use, you can add it to the Quick Access Toolbar (QAT) in Excel 2007. (If you're using Excel 2003, or an earlier version, you can customize the Excel toolbars, and add a button for the macro.)

1. At the right end of the QAT, click the drop down arrow
2. Click More Commands
3. In the Choose commands from drop down, click Macros
4. In the list of macros, click the PERSONAL.XLS!ToggleR1C1 macro
5. Click Add, to move it to the Quick Access Toolbar
   
6. In the QAT list, click the PERSONAL.XLS!ToggleR1C1 macro
7. Click Modify, and click on an icon for the macro (I use the 8-ball), then click OK
   
8. Click OK, to close the Excel Options window.

Step 5: Test Your Macro to Switch Column Headings to Numbers

On the QAT, click the new button, to toggle the reference style setting between A1 and R1C1.

Congratulations! Your Excel VBA work is done, and now you can easily switch column headings to numbers.

Watch the Video

To see the steps for adding the macro to the QAT, watch this short video.

______________