Solved: Convert Monthly to Quarterly using Macro

[Idiot]

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(B24)" 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.

[Idiot]

Since I'm unable to attach a file or provide a direct link. Please, I beg you with much appreciation, to check out wikisend site. Click download and then use this id: 522734 to grab the file. I'm sorry I couldn't make it easier, I didn't know what else to do. And I don't want to spam the forums.

[mdmackillop]

You can attach a file using Manage Attachments in the Go Advanced reply section

[Idiot]

You can attach a file using Manage Attachments in the Go Advanced reply section

*Gasp* it worked this time. I swear I'm not lying when I say i tried before, but it kept saying upload in progress. I left the window open for 5 minutes, but nothing changed. (And yes I did hit the upload button)

Thank you, it should be attached now

[mdmackillop]

No need for VBA
Enter this in a cell B24 and copy across and down to M27

=SUM(OFFSET($B$2:$D$4,3*(ROW()-24),3*(COLUMN()-2)))

[Idiot]

that is AWESOME. Can I also offset the rows too? I basically changed it to have the offset reference to:
=SUM(OFFSET($B2:$D4,0,3*(COLUMN()-2)))

It does the first group of rows perfectly but if I drag down, the offset is only by one. How do I make it so it's the same offset as the columns?


You are awesome by the way.. thank you. I'll propose this as a solution and see if I can avoid macro.

[mdmackillop]

I missed the row offset and edited my post to show the row offset solution. If you need vba, though, let us know

[Idiot]

you ARE AWESOME. Thank you soo soo much. I've been pulling hair out (not mine ). If I still need to make a macro, i think I might be able to put something together. It probably won't be as efficient as what you guys can do. But I think I can take this and implement it. You guys are the best. Thank you.

[mdmackillop]

[VBA]Sub LoopTest()
Dim r%, c%
For r = 2 To 20 Step 3
i = i + 1
j = 0
For c = 2 To 100 Step 3
j = j + 1
Range("A24").Offset(i, j).Formula = "=SUM(" & Cells(r, c).Resize(3, 3).Address & ")"
Next c
Next r
End Sub[/VBA]

[Idiot]

Cool.

Is there a way I can have the user select the entirety of the data and have the macro loop through it? Unfortunately, no one else understands what to do. If it was just for me, this would be absolutely perfect.


I'm seriously learning a lot here. With every code snippet, I learn something new. So much better than a book.

[mdmackillop]

Not a problem. Where would the results be posted? Relative to the selection (eg 3 rows below the first column) or starting in a specific cell (eg B24)?

[Idiot]

Wow, you're a really nice guy. Thank you.

Well, just a couple rows below the selected area I think is fine. Sometimes I will have other data that's below that, but I don't want to ask for something that's too complicated and have it ask the user with a prompt or something. So just below the selected area is fine.

Thank you soooooo much.

[mdmackillop]

[VBA]
Sub LoopTest()
Dim a%, b%, r%, c%, i%, j%
c = Selection(1).Column
r = Selection(1).Row
cls = Selection.Columns.Count
rws = Selection.Rows.Count

For a = c To c + cls - 1 Step 3
For b = r To r + rws - 1 Step 3
Cells(r + rws + 3, c).Offset(j, i).Formula = "=SUM(" & Cells(b, a).Resize(3, 3).Address & ")"
j = j + 1
Next b
i = i + 1
j = 0
Next a
End Sub
[/VBA]

[Idiot]

You are a savior, thank you! Your code taught me some new things too.

So awesome. Thank you.