Varying the cell range around a sum formulae

[JZB]

Hi guys

I am putting a sum formulae at the bottom of a column. However the data varies in length. the start point is always row 2, but the lastrow variable needs to be incorporated into the formula

I got this so far

nlastrow = Range("C65536").End(xlUp).Row

Range("C" & nlastrow + 2).FormulaR1C1 = "=SUM(C2:"C"&nlastrow)"

but i keep getting an error. :banghead:

can anyone help

Thanks

Jon

[mdmackillop]

You are not using R1C1 references so

Range("C" & nlastrow + 2).Formula = "=SUM(C2:C" & nlastrow & ")" 'Edit: corrected
'or
Range("C" & nlastrow + 2).FormulaR1C1 = "=SUM(R2C:R[-2]C)"

[JZB]

cheers, i am getting a syntax error on Range("C" & nlastrow + 2).Formula = "=SUM(C2:"C"&nlastrow)"

are the speech marks incorrectly applied?

[mdmackillop]

"=SUM(C2:C" & nlastrow & ")"

[JZB]

thanks thats great.

one last thing if poss. i wish to move this across the columns?

we covered it in another thread but not with the nlastrow variable.

[JZB]

Range(cells(nlastrow+2,3),cells(1,columns.Count).end(xltoleft)).Formula = "=SUM(R2C:R[-2]C)"

was my best guess