How to make your R1C1 VBA formulas easier to Read

You hate those horrible R1C1 formulas that your Excel recorder produces, right? Like this one…

Range(“B11”).FormulaR1C1 = “=SUM(R[-9]C:R[-3]C)”

Me too!

And the reason we hate that notation that much is because we are so accustomed to work with “A1” formulas in Excel and because this style of cell referencing is friendlier.

Here’s a nice trick (Convert formula method) to convert R1C1 formulas into A1 formulas easily.

For example: to convert this awful formula =VLOOKUP(RC[-1],R[-2]C[-6]:R[2]C[-4],2,1) into its “A1” equivalent, use the ConvertFormula statement this way…

1) Open the VBA editor by pressing CTRL+F11

2) Launch the Immediate Window

Do it this way…Press CTRL+G or go to View>Immediate Window…
Press CTRL+G

3)In the Immediate Window enter:

debug.print Application.ConvertFormula( formula:=”=YOUR_R1C1_FORMULA_GOES_HERE”, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

For our example today, enter this…

debug.print Application.ConvertFormula( formula:=”=VLOOKUP(RC[-1],R[-2]C[-6]:R[2]C[-4],2,1)”, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

You can use any Excel formula here

Immediate window, Application.ConvertFormula

4) and then press Enter

so you the immediate window returns =VLOOKUP(G16,B14:D18,2,1)

Press enter and get the results

Immediate window results

Did I leave something out? Please tell us your ideas in the comments section below…