Autosum a variable column length using VB

[sid]

Please can anyone help me.
I am trying to autosum a column in Excel using Vb.
I can get the sum to work if I sum the range from J4 to the very last
cell J65535.

I am using shts.Range("I4").End(xlDown) to find the last empty cell of
the column. I have found this to work and get me to the cell I want to
input the Autosum.

I would like to not use the last Cell way J65535 but use the xldown way
to sum or if possible get to the cell I want to input the data and just
autosum on the cell.

By recording a macro doing this I have found it uses R1C1 formula
Selection.FormulaR1C1 = "=SUM(R[-65535]C:R[-1]C)"
but as I do not know in advance how many rows for each contract on a
worksheet and I would have to use the last row again.

Finaly as the workbooks are sent on via email and will be edited later I
would like to have the sum as a formula in the cell and not just the
sum. eg. "=sum(J4:J65535)"


Method I am trying to do.
Set Rge = shts.Range("I4").End(xlDown) 'This sets the last cell of the
range.

'this calculates from the first cell of the range J4 to the last Cell
shts.Range("J4").End(xlDown).Offset(1, 0).Value = "=sum(J4:Rge)"
.......................................................................
This works but it calculates from the first row of data to the very
last row of the column
shts.Range("J65536") = "=sum(J4:J65535)"
shts.Range("J4").End(xlDown).Offset(1, 0).Value = shts.Range("J65536")

Any help would be appreciated.
Thank you.

regards,
Sid.

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tom Ogilvy]

shts.Range("J4").End(xlDown).Offset(1, 0).FormulaR1C1 = "=sum(R4C:R[-1]C)"

 

[Ardus Petus]

dim Rng as range
set rng=Range("J4").end(xldown)
rng.offset(1,0).formula="=SUM(J4:J"&rng.row&")"

HTH

 

[sid]

Thank you Ardus for your help.
I used Toms method and it works perfect.
I will keep your code in case I get any troubles
Thank you.



*** Sent via Developersdex http://www.developersdex.com ***

 

[sid]

Thank you Tom,
it works perfect and it puts the formula into the cell.
I have spend hours trying to get it to work and began to think it was
impossible.

Thank you.

Regards,
Sid.



*** Sent via Developersdex http://www.developersdex.com ***