• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

My Macros are stuck recording in R1C1 style

R

Rej Lapointe

New Member
Hi,
I'm not sure how it happen but any time I record a macro now I get the R1C1 style instead of the A1 style.

Ex:

Normally I would have recorded ActiveCell.FormulaR1C1 = "=B5&D5"
but now I get ActiveCell.FormulaR1C1 = "=RC[-1]&RC[-3]"

Any one knows how I turn this off?

BTW: I use R1C1 style when I need it. But I do not want it all the time.
 
I think you can turn off this feature in excel options. In excel 2010, File->Options->Formulas, then there you can see "working with formulas" in that R1C1 reference style. Just remove click on that.

May be this is the right way to turn off the R1C1 style. Please try and share
 
No this is not it. That is for seeing the R1C1 style in the worksheet as you type formulas. It does nothing to the code when you write a macro.
 
Thanks Narayank991,
Yes I saw this post a while back but it's not it either.

When that button is clicked in or not changes this:

Relative Reference ON - The button is clicked in
Sub Macro5()
ActiveCell.FormulaR1C1 = "Ref clicked in"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-3]+RC[3]"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Relative Reference OFF-The button is clicked out
Sub Macro6()
Range("J19").Select
ActiveCell.FormulaR1C1 = "Ref Clicked out"
Range("J20").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]+RC[3]"
Range("J22").Select
End Sub

My issue is this parts "=RC[-1]+RC[-2]+RC[3]"

I've seen many post and people always get around two two things mentioned above and I never found the solution.
 
Hi,
to my knowledge, you can switch between absolute/relative reference only for the "selection" of cells.
All formulas are recorded using the R1C1 style in VBA.

If you want to see the formula in A1 style, type this command in the execution window :
? activecell.formula
 
Hi ,

You are right that you saw the post a while back ! let me quote from there :
I believe you cannot do that. The macro will always record in R1C1 style.

You can always switch the style but it will only be applied to the worksheet and if you record a macro it will still show R1C1 reference style.
Click to expand...
Narayan
 
@Rej

as per my knowledge there is no option to turn off the R1C1 Style in VBA or VB so when ever we record a macro through record macro from excel file then it by default it's take the R1C1 Style but the formula is working as we desire. if you want to know the formula then select the Range A1 and type in Immediate Window as ?ActiveCell.formula and press enter then you will get the accurate formula in the Immediate window. If you want to put the formula in VBA then you have to write the code in VBA

i just check your 2nd part so you add the below code

Code: 
Sub Macro6()
Range("J19").Select
ActiveCell.Formula = "Ref Clicked out"
Range("J20").Select
ActiveCell.Formula = "=XFD5+XFC5+D5"
Range("J22").Select
End Sub

Hope it clear, if not please post a sample file with your required result

Thanks

SP
 
Hi, Rej Lapointe!

I entered 2 values in A1 and A2 cells, selected A3 cell, tried recording a simple macro and surprisingly I got this:
Code: 
Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
    Range("A4").Select
End Sub

Where I expected to get:
Code: 
Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.Formula = "=A1+A2"
    Range("A4").Select
End Sub

Either there's a long time that I don't use that kind of macro recording (everyday I use the macro recorder but not for that kinda stuff) or something has changed in 2013 version (which I installed a couple of months ago and I hate it more every second).

I'd have sworn, in fact I swear that I never read such a code in R1C1 format, otherwise I'd have made a lot of voodoo dolls for all and every Redmond guy involved in that change.

Regards!
 
I also faced the same problem. Though I do not remember exactaly when it changed to R1C1 from A1.
If we have a big formula it gets very confusing. Also this is prone to errors.

To avoid confusing myself, I write the code in following manner.
Record the macro (if you want to). This record will give you proper structure for the code.

In our example,
Code: 
Option Explicit
Sub Macro1()
' Macro1 Macro
    ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
    Range("A4").Select
End Sub

I write the formula in excel file in normal manner.

Then for including this formula in VBA. Do following steps manually
1) Select the cell
2) Press Function key F2
3) Copy the formula using Ctrl Shft Right Arrow
4) Overwrite the formula in VBA with this formula

So I will replace "=R[-2]C+R[-1]C" with "=A1+B1". And the code will look like

Code: 
Sub Macro14()
    ActiveCell.Value = "=A1+B1"
End Sub
 
You must log in or register to reply here.
Back
Top