Macro records in R1C1 style

[Otto Moehrbach]

Excel 2002, Win XP
When I record the macro to simply put:
=A1+B1
in a cell, I get:
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"

This macro does put =A1+B1 like I want, but why is the actual code written
in R1C1 style? It is difficult to read later. I realize that I can
manually change the code line to look like I want, but in a complex formula
this can be tiresome and prone to errors. Thanks for your help. Otto

 

[Otto Moehrbach]

David
I appreciate your response. It appears you and I have a disconnect or
I'm missing something in your response. My problem is not related (I think)
to relative/absolute addressing in the recorded macro. It's related to how
the code is written. Excel insists on writing the code as:
ActiveCell.FormulaR1C1 = "=R[1]C+R[1]C[1]"
and I want:
ActiveCell.Formula = "=A9+B9".

I did what you suggested but the recorder does the same. Thanks again.
Otto

Hi Otto,

The STOP Recording dialog screen also has the Relative References.
If this box was closed during the recording of a macro you will no longer
have a choice. To get the toolbar back in sync with recording, start the
macro recorder, use View|Toolbars and select the "Stop Recording"
toolbar. Then stop the recorder.

Press the Relative Reference button and you are back in business.

More information in Excel HELP:

look for this topic in HELP
"Record a macro"
the quickest way there would be to type
Record a macro
into the Answer Wizard in HELP.
=
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Excel 2002, Win XP
When I record the macro to simply put:
=A1+B1
in a cell, I get:
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"

This macro does put =A1+B1 like I want, but why is the actual code written
in R1C1 style? It is difficult to read later. I realize that I can
manually change the code line to look like I want, but in a complex formula
this can be tiresome and prone to errors. Thanks for your help. Otto

 

[David McRitchie]

Hi Otto,
You're right. Tested (Excel 2000) and did not get a change in formula,
did see a change in the selection. Sorry could not come up with your
desired ActiveCell.Formula = "=A9+B9".

Sub Macro9()
' with Relative References NOT Depressed
Range("C2").Select
Selection.ClearContents
Range("A2:C2").Select
Range("C2").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("A2:C2").Select
End Sub
Sub Macro10()
' with Relative References depressed
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -2).Range("A1:C1").Select
ActiveCell.Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(0, -2).Range("A1:C1").Select
End Sub