convert a formula R1C1 to formula of A1 format

[roypogo]

How can I convert a formula R1C1 to formula of A1 format.

I have a formula as:
Worksheets("View Data").Range("D2").FormulaR1C1 = "=MID(Data!RC[" & Var1 & "],(LEN(Data!RC[" & Var1 & "])-9),9)"

to

Worksheets("View Data").Range("D2").Formula = "=MID(Data!F2,(LEN(Data!F2)-9),9)"

the column F will be dynamically generated from Var1.

Thanks

 

[Norie]

Which column will the formula be going in?

 

[roypogo]

The formula will be going to Column D

 

[Norie]

Which column will the formula in column D look at?

PS R1C1 formulas are easier to work with when columns are dynamic.

 

[roypogo]

The formula will look at a variable column. I tried with the below code as:
Sheets("Data").Activate
Set NM1L = ActiveSheet.UsedRange.Find("NM1*IL*1 - Member Name", , xlValues, xlWhole)
NM1L1 = NM1L.Column - 1
MsgBox NM1L.Column.Address
Worksheets("View Data").Range("D2").FormulaR1C1 = "=MID(Data!RC[" & NM1L1 & "],(LEN(Data!RC[" & NM1L1 & "])-9),9)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & lRow), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("D2:D" & lRow), Type:=xlFillDefault
Range("D2:D" & lRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

But I am unable to get the desired result.

Actually the formula is trying to find the column in Work sheet "Data" where the header is NM1*IL*1 - Member Name. Let us say it is column F.
In Another Worksheet "View Data" in column D it will be doing a MID function of F2.

But when use my above formula instead of F2 the Mid Function is getting applied to I2 as =MID(Data!I2,(LEN(Data!I2)-9),9) instead of =MID(Data!F2,(LEN(Data!F2)-9),9)

Any help is greatly appreciated.