how to switch off R1C1 reference style in Excel 2007

Question

hi, i'm using excel-2007. i wanted to see smth with R1C1, then i checked the "R1C1 Reference Style" (office button->Excel Options->Formulas->R1C1 Reference Style)...

Now i wanted to move again back to xlA1 style, i unchecked the R1C1 Reference Style, but my macros are still written in the R1C1 style... how can i switch to xlA1 reference style so that my macros again written in the xlA1 style? thanks

i tried recording macros again it does not work!

Answer 1

You will have to manually edit the code [scratch out]or record the macros again[/scratch out].

---

EDIT:

The Office Button>Excel Options>Formulas>R1C1 Reference Style option only applies to the reference style as it appears in a cell on a spreadsheet.

With the R1C1 reference style off, I recorded a macro in which cell "A2" is initially selected. After the recorder starts, I enter the following forumla into "A2": =A1+12, press enter and stop the recorder. The recorded code is:

ActiveCell.FormulaR1C1 = "=R[-1]C+12"
Range("A3").Select

With the R1C1 reference style on, the code looks exactly the same:

ActiveCell.FormulaR1C1 = "=R[-1]C+12"
Range("A3").Select

The reason is that macro recorder always stores the formula in the FormulaR1C1 property of the ActiveCell object.

One would have to manually edit the code like this to be in xlA1 style:

ActiveCell.FormulaR1C1 = Range("A1").Value + 12