Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: R1C1 notation

  1. #1
    Join Date
    10th March 2005
    Posts
    154

    R1C1 notation

    hi there,

    I have just started to learn VBA and am a little confused by the code of the macro that I have just recorded. I just wrote the formula : =left(A1,3) - to return the first three letters of cell A1. The code that came up was :

    VB:
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = [color=blue] "=LEFT(RC[-1],3)" [/color] 
    Range("B2").Select 
    End Sub 
    
    
    The second row of the code : "=LEFT(RC[-1],3)" .

    Is this R1C1 notation. I didnt expect the code to look like this.

    Thanks a lot guys.

    marcus.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,743

    Re: R1C1 notation

    Hi Marcus,

    Yes, that's R1C1 notation.

    If you prefer you can convert:
    Sheet1.Range("B1").FormulaR1C1 = "=LEFT(RC[-1],3)"

    into:
    Sheet1.Range("B1").Formula = "=LEFT(A1,3)"

    You may find it useful, especially with more complex formulae, to become familiar with the ConvertFormula Method. This will do the conversion for you.

    For example, in the Immediate Window enter:
    debug.print Application.ConvertFormula( formula:="=LEFT(RC[-1],3)", _
    fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
    and then press <Return>

    You will be presented with : =LEFT(A1,3)

    HTH
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  3. #3
    Join Date
    10th March 2005
    Posts
    154

    Re: R1C1 notation

    Great - thanks for your help. So does this mean that the R1C1 formula is the type that you must use when you type the code or is just the default code type that the system will use when i record a macro.

    thanks again,

    marcus

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,563

    Re: R1C1 notation

    As Bill and Tracy note in their book, VBA and Macros for Microsoft Excel, it is often advantagous to use the R1C1 method, because it allows more flexibility. And despite what you write and visibly see, MS Excel still uses R1C1 style under the covers.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

  5. #5
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,743

    Re: R1C1 notation

    Hi Marcus,

    You can use either Formula or FormulaR1C1 when you are coding. The MacroRecorder will use R1C1 when you enter formulas.

    There are times, as Shades suggests, when the flexibility of the R1C1 approach can be a big advantage.
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  6. #6
    Join Date
    10th March 2005
    Posts
    154

    Re: R1C1 notation

    . . .guess never having seen it before though it is a bit confusing. Will just have to get used to it though. thanks.

    So do you guys/girls have any other tips for a beginnner. Good books etc I have Excel 2002 power programming - John Walkenbach - i think it is quite good but any tips would be gold - thanks.

    marcus

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,743

    Re: R1C1 notation

    Hi Marcus,

    Yeah, anything by JW is going to be good.

    As good as books are, the two most valuable tools are available to you within Excel - the Help files and the MacroRecorder (which I'm pleased to see you are already using ). Both of these remain useful even when you progress beyond beginner status.

    Don't be one of those coders that thinks they are too good for Help files and the Recorder - they really are very useful and you'll find that most of the more experienced coders still make use of each of them (not as frequently, admittedly, but they are still used).

    Final tip - practice. Visit forums like this one and try to solve the problems posed. You don't have to post any suggestions right away, just try to work out how you would approach the problem and then compare your approach to that suggested by others.

    HTH
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  8. #8
    Join Date
    6th May 2005
    Posts
    17

    Re: R1C1 notation

    For the newbies, I was reading in VBA and Macros for Microsoft Excel two days ago and found this on R1C1. The R1C1 style formulas is discussed starting on page 123. It was extremely helpful to me.

    Makes it so easy to understand...

    A R1C1-style reference includes the letter R to refer to Row and the letter C to refer to Column.

    When you use R1C1 with Relative References: For columns, a positive number means to move tot he right a certain number of columns, and a negative number means to move to the left a certain number of columns. From cell E5, use RC[1] to refer to F5 and RC[-1] to refer to D5.

    For rows, a positive number means to move down the spreadsheet a certain number of rows. A negative number means to move toward the top of the spreadsheet a certain number of rows. From cell E5, use R[1]C to refer to E6, and use cell R[-1]C to refer to E4.

    If you leave off the square brackets for either the R or the C, it means that you are pointing to a cell in the same row or column as the cell with the formula.

    A few other examples:

    If you enter =R[-2]C[-1] in cell E5, you are referring to a cell two rows up and one column to the left. This would be cell C4.

    If you enter =RC[-1] in cell E5, you are referring to a cell in the same row, but one column to the left. This would be cell D5.

    If you enter =RC in cell E5, you are referring to a cell in the same row and column, which is cell E5 itself. You would generally never do this because it would create a circular reference.

    Hope this helps you understand R1C1.

    Saylor

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th March 2005
    Posts
    154

    Re: R1C1 notation

    thanks loads for the advice - hopefully i can stick with this and get enough cool practice.

    cool - much appreciated

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    Re: R1C1 notation

    Quote Originally Posted by badger55
    So do you guys/girls have any other tips for a beginnner. Good books etc I have Excel 2002 power programming - John Walkenbach - i think it is quite good but any tips would be gold - thanks.
    marcus
    Marcus,

    Richie, Shades and Saylor seem to have given you a good bit of input. I'll add mine too if you don't mind.

    Walkenbach's book's are all awesome. If you are really wanting to learn how to code, they are a great place to start. The VBE (the editting window where you write your code) can help you understand alot as well. Roy has shown how the Immediate window can be a great deal of help, so can leaving the Properties window open (I know some who close it all the time, don't know why though).

    The R1C1 style of referencing takes a bit of getting used to, but it is worth learning. Saylor's contribution is a great way place to start in mentally translating what is being referenced.

    The more code you see, the more you will learn. To that end, exhaust the resources here at Oz, they are plentiful and high quality. Other quality places to see code are:

    Buy Walkenbach's PUP utility WITH the source code. Very good coding for learning is in there.

    Go here VBA Express

    Google search for VBA code.

    Quote Originally Posted by Saylor
    For the newbies, I was reading in VBA and Macros for Microsoft Excel two days ago and found this on R1C1. The R1C1 style formulas is discussed starting on page 123. It was extremely helpful to me.
    Shouldn't all Excel books leave this page blank?
    Last edited by Brandtrock; May 7th, 2005 at 06:03. Reason: Correct reference to Roy to Richie

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. FormulaR1C1 returns R1C1 notation
    By Robts in forum Excel General
    Replies: 6
    Last Post: June 27th, 2006, 11:56
  2. R1C1 notation to check for values?
    By J88L in forum Excel General
    Replies: 2
    Last Post: December 22nd, 2005, 02:00
  3. Variable / Loops with R1C1 notation
    By ORANGEBEE in forum Excel General
    Replies: 4
    Last Post: October 11th, 2005, 23:11
  4. Autofill two rows using r1c1 notation.
    By Lera in forum Excel General
    Replies: 3
    Last Post: November 23rd, 2004, 22:47
  5. Replies: 1
    Last Post: September 6th, 2003, 05:34

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno