"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

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…

Dare to simplify your workflow, get more done with less, and be seen like an Excel Authority?

Download my report 'How to Think Like An Excel Guru'!

Works great for intermediate and advanced Excel users!

We promise to do our best to keep your email safe