Jan 21 2020 04:11 AM
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.
Jan 22 2020 02:29 AM
@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)"