Understanding Code for References

[Mordred]

I have some a question or two regarding some code that I recorded:

Please Login or Register  to view this content.

I don't really understand how this works. Specifically, the "=Cansim!R[-1]C" * 3and "=Cansim!R[-14]C" * 3. What I don't get is, each one of the six bits of code represent different cells from a different sheet but yet they appear to be repetitive. I want to implement this type of reference into a macros but I really don't understand the logic here.

Regards

[romperstomper]

R1C1 referencing uses row and column numbers. If the number (row or column) is in square brackets then it is relative to the cell containing the formula. So:
=Cansim!R[-1]C
means the cell on Cansim sheet one row above the cell containing this formula and in the same column. (just using R or C is equivalent to R[0] or C[0])
=Cansim!R1C
would refer to the cell on Cansim in row 1 of the same column the formula is in.
=Cansim!R1C1
would refer to the cell on Cansim in row 1 and column 1 (i.e. the equivalent of Cansim!$A$1 in A1 style notation.
Does that make sense?

[Mordred]

Thanks romperstomper,
I think it does. So if I want and active cell from one sheet at Range("E91") to = the cell from sheet Cansim to = Range("E11"), then the code would be
=Cansim!R[-80]C Right?

Similarly, if I wanted a cell from one sheet at Range("E91") to = the cell from sheet Cansim to = Range("E12") the the code would be
=Cansim!R[-80]C[1]
Am I understanding this correctly?

[romperstomper]

Almost - it would be:
=Cansim!R[-80]C
if you used C[1] it would be one column to the right.
Though it depends if you want a relative formula or an absolute one.
The above is equivalent to entering:
=Cansim!E12
in the cell. If you wanted:
=Cansim!$E$12
(so it would not alter when copied from row to row or column to column), then it would be:
=Cansim!R12C5

[Mordred]

Hi romperstomper,
Just to clarify, you said:

If you wanted:
=Cansim!$E$12
(so it would not alter when copied from row to row or column to column), then it would be:
=Cansim!R12C5

Is "=Cansim!R12C5" the same as:
"=Cansim!R[-80]C[1]"

It looks like it would give the same outcome given the active cell on the other sheet.
I don't want to pound you with what may be redundant questions but I want to fully understand!!

PS. You've helped so much with this and I really appreciate it!

[romperstomper]

They're not the same, no. One is the equivalent of Cansim!$E$12, the other is Cansim!E12

[DonkeyOte]

I'm sure R won't mind - below is a link to a very brief (and no doubt flawed) overview of R1C1 notation which may (or may not) be of interest!

http://www.excelforum.com/2171545-post2.html

edit: the link is in essence just a repetition of R's earlier post - apologies !

[Mordred]

Ok, I fully understand now. Thanks again romperstomper and thanks DonkeyOte for your help.