hornsfan01
New Member
- Joined
- Jul 4, 2012
- Messages
- 7
I'd like to have a macro that takes values in two separate columns of the same size and sums the values in each row and then replaces one of the columns with the new sums.
So what I'd like to start with is this:
A | B | |
1 | 4 | 20 |
2 | 2 | 10 |
3 | 3 | 30 |
4 | 5 | 10 |
5 | 0 | 20 |
6 | 4 | 0 |
<tbody>
</tbody>
and run a macro that adds the values in A1 and A2 and puts the value in A2 then adds the values in B1 and B2 and puts that value in B2 and so forth.
I mention values because while B1:B6 will just be integers, A1:A6 will be formulas obtaining values from another part of the sheet and these values may change every time the sheet is recalculated. I don't want the formulas copied, just the values that the formulas end up with.
So I'd like to run the macro and end up with this:
A | B | |
1 | 4 | 24 |
2 | 2 | 12 |
3 | 3 | 33 |
4 | 5 | 15 |
5 | 0 | 20 |
6 | 4 | 4 |
<tbody>
</tbody>
I just started poking around in VBA today and tried to come up with something. I set A1:A6 as rCurrentValues and B1:B6 as rCumulativeValues after defining both as variables of type Range.
But unfortunately, while rCumulativeValues.Value = rCurrentValues.Value seems to work if I want to replace one with another, rCumulativeValues.Value = rCumulativeValues.Value + rCurrentValues.Value does not (but it explains what I'm conceptually trying to do).
Can I do this with a statement along the lines of above or do I need to set up a for each loop or something like that? If I need a loop, can someone write out this example for me, as I have been trying to learn VBA loops from various websites and many seem to infer some basic degree of understanding of VBA loop syntax, of which I have none.