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

E-coli 1 Posts Wednesday August 27, 2008Registration date August 28, 2008 Last seen - Aug 28, 2008 at 03:02 PM - Latest reply: Ivan-hoe 436 Posts Saturday February 16, 2008Registration date October 17, 2008 Last seen
- Sep 3, 2008 at 04:11 AM
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]"
See more 

3 replies

Best answer
Ivan-hoe 436 Posts Saturday February 16, 2008Registration date October 17, 2008 Last seen - Sep 2, 2008 at 08:39 AM
4
Thank you
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

Thank you, Ivan-hoe 4

Something to say? Add comment

CCM has helped 1701 users this month

Ivan-hoe 436 Posts Saturday February 16, 2008Registration date October 17, 2008 Last seen - Sep 3, 2008 at 04:11 AM
1
Thank you
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
0
Thank you
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