Adding a column of cell numeric values to total within an excel macro.

Copper Contributor

Hello. I've tried to find on the web how to add cell numbers to a counter in an excel macro but can't find any examples.

I've tried defining the total field: DIM integer, long, double, variable.

I've tried defining the cell range as value, formula, without any extension.

I'll post a piece of my program below. Please help, thank you:


Sub TotalAccount()
' TotalAccount Macro
' "G" row cells formatted as numeric, 0 decimal places
' The cell value is never more than 5000
' TotalAcct value will never exceed 500,000
' 1st 5 values in row J: 450, 10, 20, 1040, 30
' Totalacct should be 1550 but is 30 (last value in list)

Dim Cellrow As Integer
Dim Numbercell As Integer
Dim Totalacct As Integer
Totalacct = 0

For Cellrow = 5 To 104
Numbercell = Range("J" & Cellrow)
Totalacct = Totalacct + Numbercell
Next Cellrow

Cellrow = 105
Range("J" & Cellrow) = Totalacct
End Sub

3 Replies
Your code "For Cellrow = 5 To 104" starts from J5 which is 30 ...

And I would suggest use formular instead of marco, or
Range("J105") = Application.WorksheetFunction.Sum(Range("J5").Resize(100, 1))


Hello. Thank you for your answer.

I believe the DIM statement for the counter or accumulator might not be integer

but something else. Is this correct? Dim Totalacct As Integer


Also, the statement that is accumulating numbers isn't doing so:

The Totalacct variable is the accumulating the numbers in row J.

After the following: Cellrow).value   or   Cellrow).formula might

be incorrect.

Numbercell = Range("J" & Cellrow)
Totalacct = Totalacct + Numbercell


@Tommee Can't really understand why you would want to do this with a macro but that's your choice. Pasted your code into a module and filled cells J5:J9 with the numbers you mentioned. Ran the code and the result of 1550 correctly shows in J105. See attached.

Screenshot 2023-05-24 at 06.01.45.png