Rows and Columns RC[7] Solved/Closed

Question

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]"

Ivan-hoe · Accepted Answer

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

E-Coli · Answer

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

Ivan-hoe · Answer

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

Rows and Columns RC[7]

3 responses

Subscribe To Our Newsletter!