Search code examples
excelsumexcel-r1c1-notationvba

Excel VBA R1C1 formula for SUM xlUp

This code will sum the 6 cells above it in Column P of the last row+1 of the data dump. 

Sheet2.Range("A1").End(xlDown).Offset(0, 15).FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"

I need help with the R[-6]C part. The dump can have variable rows and I want to always sum all the way up to P2.

How can I write this?

Solution

  • use the absolute version R2C in place of R[-6]C

    Sheet2.Range("A1").End(xlDown).Offset(0, 15).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

    Now it will always refer to the second row of that column.