Solved

Reading RC convention

Posted on 2011-02-14
5
405 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now