Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Senior Member
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. ## 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

3. Senior Member
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. Resident Old Codger
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.

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.

6. Senior Member
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. ## 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

8. I agreed to these rules
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. Senior Member
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. ## Re: R1C1 notation

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

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.
Last edited by Brandtrock; May 7th, 2005 at 06:03. Reason: Correct reference to Roy to Richie

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

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