Announcement

Collapse
No announcement yet.

R1C1 notation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 :

    Code:
        Range("B1").Select
        ActiveCell.FormulaR1C1 =  "=LEFT(RC[-1],3)" 
        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.

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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)

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  Re: R1C1 notation

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

                  cool - much appreciated

                  Comment


                  • #10
                    Re: R1C1 notation

                    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.

                    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, 06:03. Reason: Correct reference to Roy to Richie
                    Brandtrock

                    Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

                    Comment


                    • #11
                      Re: R1C1 notation

                      Hi Brandtrock,

                      What's up with calling me Roy? Not that there is anything wrong with the name, its just not me.
                      Cross-poster? Read this: Cross-posters
                      Struggling to use tags (including Code tags)? : Forum tags

                      Comment


                      • #12
                        Re: R1C1 notation

                        Originally posted by Richie(UK)
                        Hi Brandtrock,

                        What's up with calling me Roy? Not that there is anything wrong with the name, its just not me.
                        Well that's a double dumb a$$ on me!

                        Sorry mate,
                        Brandtrock

                        Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

                        Comment


                        • #13
                          Re: R1C1 notation

                          Excellent. thanks a million for all the advice. i am really enjoying it. Want to get more stuck in.

                          Thanks all!

                          Comment

                          Working...
                          X