Excel 2007-2010 VBA Paste Chart R1C1 Link RRS feed

  • Question

  • When I'm using R1C1 instead of A1 formats (Options-Formula-R1C1) and use VBA to paste a link to a cell into a chart sheet, VBA uses an A1 link instead of an R1C1 link. Excel 97-2003 pasted correct link format. Not working in xl2007-2010.

    VBA: CHT.TextBoxes.Formula = "='" & DataSheetName & "'!" & Sheets(DataSheetName).Cells(11, DecPtsCol).Address

    Result: ='Obs 1 - Obs 5 Data'!$H$1 
    instead of ='Obs 1 - Obs 5 Data 4'!R1C8

    XL2007-2010 charts will not display A1 link when formula format is R1C1.

    Friday, July 8, 2011 3:45 PM

All replies