Hi, can anybody help me on this? I've trying to figure out how to sum up
the amount in a column. I've a source file, everytime the data in the
source file can be varied, meaning the amount in the file is not fixed.


By this, how to write a script to sum up all the amount and the total
will be below.

Hope you understand what I am trying to say.

Cheers.


--
tanks1308
------------------------------------------------------------------------
tanks1308's Profile: http://www.excelforum.com/member.php...o&userid=12362
View this thread: http://www.excelforum.com/showthread...hreadid=466016

One way:

In a new module, enter this macro, and change Sheet1 to the neam of your
worksheet, and the "A" in "A65536" to the column you need to sum.

Option Explicit

Sub Summit()
Dim lngRow As Long
Dim rngX As Excel.Range

' find the end of the list
Set rngX = Sheet1.Range("A65536").End(xlUp).Offset(1, 0)
lngRow = rngX.Row - 1
rngX.FormulaR1C1 = "=SUM(R[-" & Trim$(CStr(lngRow)) & "]C:R[-1]C)"
End Sub


Alan P.

"tanks1308" wrote
in message ...

Hi, can anybody help me on this? I've trying to figure out how to sum up
the amount in a column. I've a source file, everytime the data in the
source file can be varied, meaning the amount in the file is not fixed.


By this, how to write a script to sum up all the amount and the total
will be below.

Hope you understand what I am trying to say.

Cheers.


--
tanks1308
------------------------------------------------------------------------
tanks1308's Profile:
http://www.excelforum.com/member.php...o&userid=12362
View this thread: http://www.excelforum.com/showthread...hreadid=466016

This line:
rngX.FormulaR1C1 = "=SUM(R[-" & Trim$(CStr(lngRow)) & "]C:R[-1]C)"

could be written:
rngX.FormulaR1C1 = "=SUM(R[-" & lngRow & "]C:R[-1]C)"

And maybe just:
rngX.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
to start at row 1 through the row above.


Alan Perkins wrote:

One way:

In a new module, enter this macro, and change Sheet1 to the neam of your
worksheet, and the "A" in "A65536" to the column you need to sum.

Option Explicit

Sub Summit()
Dim lngRow As Long
Dim rngX As Excel.Range

' find the end of the list
Set rngX = Sheet1.Range("A65536").End(xlUp).Offset(1, 0)
lngRow = rngX.Row - 1
rngX.FormulaR1C1 = "=SUM(R[-" & Trim$(CStr(lngRow)) & "]C:R[-1]C)"
End Sub

Alan P.

"tanks1308" wrote
in message ...

Hi, can anybody help me on this? I've trying to figure out how to sum up
the amount in a column. I've a source file, everytime the data in the
source file can be varied, meaning the amount in the file is not fixed.


By this, how to write a script to sum up all the amount and the total
will be below.

Hope you understand what I am trying to say.

Cheers.


--
tanks1308
------------------------------------------------------------------------
tanks1308's Profile:
http://www.excelforum.com/member.php...o&userid=12362
View this thread: http://www.excelforum.com/showthread...hreadid=466016


--

Dave Peterson