Convert formula to Formula R1C1

[p9j123]

I have a dynamic formula and it is working just fine, however I want it to be converted to R1C1 formula. Can you please help me with this?

Cells(5, 7).Formula = "=VLOOKUP(F5,'[" & Range("A1").Value & "]Job Tracker'!$E$1:$F$" & a & ",2,0)"

 

[Joe4]

One easy way is to turn on the Macro Recorder and record yourself entering the formula in the cell on the Excel worksheet.
By default, it records the formula using R1C1 format.

 

[p9j123]

There are some dynamic component that cannot be recorded by the macro.

 

[Joe4]

There are some dynamic component that cannot be recorded by the macro.

No, but it is easy enough to modify the formula after recording it!
To me, that part is much easier than coming up with the R1C1 part (not that it is hard, I just always confuse the absolute vs. relative range references and how to write them).

Otherwise, read this: https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

I prefer to let Excel do most of the work (through Macro Recorder), and then just modify to suit my needs.

 

[Norie]

Try this.

Cells(5, 7).FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & Range("A1").Value & "]Job Tracker'!R1C5:R" & a & "C6,2,0)"

 

[mikerickson]

Something like

Dim a1Formula As String, r1c1Formula As String
    
a1Formula = "=A1+$B$1"
    
r1c1Formula = Application.ConvertFormula(a1Formula, xlA1, xlR1C1)

The thing about that is the relative references have to be in relation to some cell.

=A1 in B1 converts to =RC[-1], but if it were in C1 it would be =RC[-2].

The ConvertFormula function takes a RelativeTo argument, which defaults to the active cell.