Rows and Columns RC[7] [Solved/Closed]

Report
Posts
1
Registration date
Wednesday August 27, 2008
Status
Member
Last seen
August 28, 2008
-
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
-
Hello,

I am trying to do some extensive formula writing that involves complex sheet referencing. In my attempts, I do not understand the numbers that come after the R and C in the following. They do not seem to be linear like row and column headings. Could someone explain to me how these numbers work?


ActiveCell.FormulaR1C1 = "=SAF0006!R[-1]C[3]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=SAF0006!R[-2]C[7]"

3 replies

Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Hello E-Coli !
the square brackets are used for relative adressing :
if the active cell is C3 (third row / third column), R[-2]C[7] corresponds to J1 (2 rows backwards and 7 columns forward)
For absolute adressing, do not use the square brackets :
R2C7 corresponds to $G$2 (second row / seventh column)
Ivan
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2789 users have said thank you to us this month

Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
I do not have any code already compiled for what you want, but let me give you some tips
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = "MyName" ' rename the new worksheet

to repeat this as many times as you have names in your list, use a loop (e.g. For Each ... Next)
For Each MyCell In MyRange 'MyRange is the range containing your list
' instructions
Next MyCell

Ivan
Ivan-Hoe,

Thank you very much. I can't believe something so simple was causing me such problems.

I have another one for you:

How can I automatically create and name worksheets in a workbook based on a list that exists in another sheet "Summary" in the workbook? The list begins at cell A10. Mind you, this list will vary from workbook to workbook. Do you have some code already compiled to perform this?

E-Coli