Autosum a variable column length using VB

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 ***

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

--
Regards,
Tom Ogilvy



"sid" wrote:

>
> 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 ***
>

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

HTH
--
AP

"sid" <[email protected]> a écrit dans le message de news:
[email protected]...
>
> 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 ***

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 ***

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 ***