Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Command Button to Sum specific cells in a range
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Command Button to Sum specific cells in a range
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. -- Gary''s Student - gsnu200843 "npop03" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Command Button to Sum specific cells in a range
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" wrote: 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. -- Gary''s Student - gsnu200843 "npop03" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Command Button to Sum specific cells in a range
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 -- Gary''s Student - gsnu200843 "npop03" wrote: 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" wrote: 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. -- Gary''s Student - gsnu200843 "npop03" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Command Button to Sum specific cells in a range
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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200843 "npop03" wrote: 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" wrote: 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. -- Gary''s Student - gsnu200843 "npop03" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Command Button to Sum specific cells in a range
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! "npop03" wrote: 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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200843 "npop03" wrote: 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" wrote: 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. -- Gary''s Student - gsnu200843 "npop03" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modify a macro to apply to a specific range of cells | Excel Discussion (Misc queries) | |||
Macro to Specific Cells in a Range | Excel Programming | |||
Macro to Select Specific Cells in a Range | Excel Programming | |||
command button that puts specific numbers in a range of cells | Excel Programming | |||
command button to move user to specific spot on different sheet | Excel Programming |