Idiot
05-13-2010, 05:13 PM
I apologize, but I'm very new to VBA, but I am trying to learn. I'm in need of some urgent help in making a macro that will sum a range of nine cells (three columns and three rows), then move to the next block of values to sum using the same size selection (three columns and three rows).
I've attached a dummy file for reference. It also include some macro code that I tried to make. It works, but it's overly simplistic. What I mean is, my code will ONLY work for the dummy file. The amount of data that comes my way changes. So, I was thinking of having the user select all the data that needs to be calculated and have a macro loop through performing the calculations and displaying the results below the selection. But, unfortunately, I have no idea how to do that.
If you run my code on the dummy file, you can see exactly what needs to be done, it's just, my code is stupid.
Just so I can be as clear as possible, and using the dummy file as reference, I want to have the user select all the data that needs to be calculated, then run a macro that will do "=SUM(B2:D4)" then move to the next block and do "=SUM(E2:G4)" and then do the next "=SUM(H2:J4)" and so on and so forth, until everything in the selected area has been calculated and then have the results display below.
My code:
Sub LoopTest()
For j = 20 To 30 Step 3
For i = 1 To 62 Step 3
Cells(j, i) = "=SUM(R[-18]C[1]:R[-16]C[3])"
Next i
Next j
End Sub
It works, but it only works for this dummy file and displays results in steps of three rather than right next to each other. If I were to get data that go on for 72 columns or longer or 72 rows or longer or some other number or longer, my code would have to be changed each time. If I were the only one using it, it wouldn't be so much of a problem.
I didn't think this would be so hard, but heh, I was wrong.
I thank you so much for taking a look at this. I promise, once I learn VBA well enough, I'll contribute to these forums. Thanks again, I appreciate any help you guys can offer.
Oh, also I'm using excel 2007.
Tried to attach file, can't see it on my end. Apparently I need to post five times.
I've attached a dummy file for reference. It also include some macro code that I tried to make. It works, but it's overly simplistic. What I mean is, my code will ONLY work for the dummy file. The amount of data that comes my way changes. So, I was thinking of having the user select all the data that needs to be calculated and have a macro loop through performing the calculations and displaying the results below the selection. But, unfortunately, I have no idea how to do that.
If you run my code on the dummy file, you can see exactly what needs to be done, it's just, my code is stupid.
Just so I can be as clear as possible, and using the dummy file as reference, I want to have the user select all the data that needs to be calculated, then run a macro that will do "=SUM(B2:D4)" then move to the next block and do "=SUM(E2:G4)" and then do the next "=SUM(H2:J4)" and so on and so forth, until everything in the selected area has been calculated and then have the results display below.
My code:
Sub LoopTest()
For j = 20 To 30 Step 3
For i = 1 To 62 Step 3
Cells(j, i) = "=SUM(R[-18]C[1]:R[-16]C[3])"
Next i
Next j
End Sub
It works, but it only works for this dummy file and displays results in steps of three rather than right next to each other. If I were to get data that go on for 72 columns or longer or 72 rows or longer or some other number or longer, my code would have to be changed each time. If I were the only one using it, it wouldn't be so much of a problem.
I didn't think this would be so hard, but heh, I was wrong.
I thank you so much for taking a look at this. I promise, once I learn VBA well enough, I'll contribute to these forums. Thanks again, I appreciate any help you guys can offer.
Oh, also I'm using excel 2007.
Tried to attach file, can't see it on my end. Apparently I need to post five times.