Explain what is what

[g48dd]

Excel 2003:

Sub Macro1()
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[6]C[1]"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[5]C[2]"
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[4]C[3]"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[3]C[4]"
    Range("A14").Select
End Sub

I don't write VBA but I look at this stuff and try and figure it out, it at least helps me make adjustments so others here don't have to do everything for me. This little mcro starts on sheet1 Cell A10, the formula is taking a sum from sheet2. There are some random numbers on sheet2 range Sheet2!B10:E16. There are 4 columns each with a SUM, the SUM is located on line 16, B16:E16. I used the macro recorder for this.

I can see Range("10").Select, that is where I want the result to appear, it is also the cell I am writing the formula in. The formula is Sheet2!B16 its just picking up the SUM of a column. What I don't understand is "=Sheet2!R[6]C[1]", there is nothing on row6? the range begins in columnB not C1? So I thought maybe it is looking at the range itself not the actaul cell address. In which case if you count the first row of the range B10:E10 as 0 you would then have the SUM I am trying to get is located in ROW6 of the range and it is in Column 1 of the range, do I have this correct?

There is only one range on sheet two. How does the VBA know where the range is, I don't see where it gets the info on where the range is located?

Thanks
Ken

 

[VoG]

Those formulas could be entered without selecting, e.g.

Range("A10").FormulaR1C1 = "=Sheet2!R[6]C[1]"

Those are R1C1 (relative) formulas - Sheet2!R[6]C[1] means the cell on Sheet2 6 rows down and one column to the right of the cell containing the formula (A10) - i.e. B16.