VB script to sum up amount

[tanks1308]

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.

 

[Alan Perkins]

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.

 

[Dave Peterson]

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.

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.

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.