Forum Discussion

MalikIF's avatar
MalikIF
Copper Contributor
Jan 21, 2020

Recording Excel Macro in R1C1 Style

Dear Microsoft tech community,

 

Assistance need, recent update on Office 365, Excel macro recording is done on R1C1 style.

    Range("H8").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C8,R2C1:R15C5,3,0)"


How I can convert this back to A1 style
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(H5,A2:E15,3,0)"

 


- I have removed the Tick from
        Options > Formulas > R1C1 reference Style.

 

- I have deleted and recreated New “Personal.Xlsb”  in the XLSTART folder.

 

- Tried below commend;
      1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      2. Application.Calculation = xlR1C1
3. End Sub

 

I am really stuck and I use macro on almost daily basis to expedite Purchasing/stock materials reports.

 

Attached sample.

Appreciate your guidance and support.

Macros and VBA

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 22, 2020

    MalikIF The macro recorder records formulas in R1C1 style, there is no way to change that I'm afraid. The up side of that is that if you have to change the location where the formula is written, the formula in the macro does not need to be edited. To set formulas in A1 style, edit your code to:

    ActiveCell.Formula = "=VLOOKUP(H5,A2:E15,3,0)"

     

Resources