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

Autosum a variable column length using VB
 

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

Autosum a variable column length using VB
 

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

HTH
--
AP

"sid" a écrit dans le message de news:
...

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

Autosum a variable column length using VB
 

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

Autosum a variable column length using VB
 

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