Macro Command Button to Sum specific cells in a range

[npop03]

Hello all,

I am trying to create a command button that will sum a specific cell to a
"total" cell located beneath it. For instance, I will have numbers ranging
from B3 all the way to CB3 and want the values in these cells to be added to
the corresponding cells in, say, B16 through CB16. Therefore, whatever is in
cell B3 will be added to cell B16 and that will be the new value in cell B16,
and so on. I would like to make this dynamic, as the data may extend past
column CB. Also, I will have other data to add in rows 4 through 12.

Currently, I am doing this very inefficiently by summing each cell, so the
code is very repetitive and long. Any suggestions?

 

[Gary''s Student]

This assumes that the destination is always a part of row 16. So if B8 thru
Z8 are selected and the macro is run, the contents of B16 thru Z16 are
updated:

Sub addum()
Set r = Selection
For Each r In Selection
c = r.Column
Cells(16, c).Value = Cells(16, c).Value + r.Value
Next
End Sub

Assign the macro to a button in the usual way.

 

[npop03]

I think we're close. Your macro works so that the total of my selection is
added to C16. I will try to explain a little better as to what I need:

I would like cell B3 to add to cell B16, cell B4 adds to B17, B5 adds to
B18, all the way down to where B12 adds to B25. Then, I need that formula to
go from Column B to as far as my data will go (currently CB). So C3 adds to
C16, C4 adds to C17, etc.

I hope this helps clarify my situation - thanks for the help!

 

[Gary''s Student]

Sub addum()
Set r = Range("B3:CB15")
For Each r In Selection
c = r.Column
Cells(16, c).Value = Cells(16, c).Value + r.Value
Next
End Sub

'a single line change

 

[npop03]

That would work, but I found it is better for me to set my range for each row
and have a "For Each" statement for each row that will correspond to the row
I want to be the total sum. However, using this method, the macro calculates
to the last column available (IV). So, my calculations are correct, but I get
an infinite number of 0s all the way to the last column.

What code should I use to make the command button only sum those cells with
values and stop when it reaches the end of my data? IF statement? Other
suggestions? If possible, please write it into my current code so I can see
exactly how it would work. Thanks!

Here is what I have so far:

Private Sub CommandButton3_Click()
Range("A16:A25").ClearContents
Set a = Range("3:3")
Set b = Range("4:4")
For Each a In Range("3:3")
c = a.Column
Cells(16, c).Value = Cells(16, c).Value + a.Value
Next
For Each b In Range("4:4")
c = b.Column
Cells(17, c).Value = Cells(17, c).Value + b.Value
Next
End Sub

I have names in A16 to A25. I found that if I clear the contents, they
automatically get replaced when I click the command button.

 

[npop03]

OK - I think I got it; I embedded an IF statement after every "For Each"
statement stating that if the cell in the range is blank, then it keeps the
other cell blank (versus a zero) Else it does what it is supposed to do and
add the two cells together.

Thank you for your replies, and if you can think of an easier or better way
to do this, feel free to post. Thanks again!