Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro records in R1C1 style
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro records in R1C1 style
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 "David McRitchie" wrote in message ... 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 "Otto Moehrbach" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro records in R1C1 style
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 --- 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 "Otto Moehrbach" wrote... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT & R1C1 Ref style | Excel Worksheet Functions | |||
R1C1 reference style | Excel Discussion (Misc queries) | |||
How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
R1C1 reference style | Excel Discussion (Misc queries) |