Solved

Reading RC convention

Posted on 2011-02-14
5
410 Views
Last Modified: 2012-05-11
Hi,

Im going through other peoples code and in the editor im trying to work out what the macro is doing

One example is

=ISERROR(IF(RC[-1]="","New Item",IF(RC[-1]=RC[-42],"No","Yes")))

How do i read that RC-1 AND RC -42?

Does it mean rows/columns, what is the above doing in terms of letters?
0
Comment
Question by:Seamus2626
5 Comments
 
LVL 6

Assisted Solution

by:FernandoFernandes
FernandoFernandes earned 200 total points
ID: 34890072
the RC referencing style is very interesting way to deal with relative referencing.
R1C1 is cell A1
R5C10 is cell J5
but when you start using the square brackets, you are referencing relatively, and it will always depend on where you're putting the formula, each cell will have a different behavior.
Example:
R[1]C = means same column, one row down
R[-1]C = means same column, one row above
RC[5] = means same row, 5 columns to the right
RC[-1] = means same row, 1 column to the left
RC[-42] = means same row, 42 columns to the right. This means that if you put this formula anywhere before column AP, you'll get a #REF errors...

is it clearer now ? :)
0
 
LVL 4

Assisted Solution

by:abitoun
abitoun earned 100 total points
ID: 34890077
RC is exactly the current row column position so the same row column to the left (current position minus one) is null then the current position will read new item, if the row column to thel eft es equal to the row columnt 42 places to the left then the current position will read No otherwise Yes.
0
 
LVL 33

Accepted Solution

by:
jppinto earned 200 total points
ID: 34890098
Please read this article to understand about R1C1 references:

http://excel.tips.net/Pages/T003010_Understanding_R1C1_References.html

Also this code from byuntd will convert references from R1C1 to A1 and vice-versa:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=254

jppinto
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34890121
BTW, if your Excel is showing you the formulas like that, you'd better change the Referencing Style to the standard one (rows with numbers and columns with letters).

On Excel 2003:
Tools / Options / General / R1C1 reference style (must be unchecked)

On Excel 2007 and 2010:
Office button /  Excel Options / Formulas / Working with formulas / R1C1 reference style (must be unchecked)
0
 

Author Closing Comment

by:Seamus2626
ID: 34890139
Thanks guys!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question