Solved: Monthly to Quarterly Part 2

[Idiot]

I've came, I've learned, and You guys conquered my problems. I hope I can rely on you guys again and apologize for not contributing yet (still learning).

I suppose I'll get right to it. A month or so ago I came to you guys with this problem: http://www.vbaexpress.com/forum/showthread.php?t=32096

And you guys did great in helping me. Now the macro has to be done in a different way. For quick reference, say we have some data:

1 13 25 37 49 61
2 14 26 38 50 62
3 15 27 39 51 63
4 16 28 40 52 64
5 17 29 41 53 65
6 18 30 42 54 66

(sorry for the format, i forgot what to use (tableit or something))

Instead of doing the sum of A1:C3 for the first block values, then the sum of D1:F3, for the next, then A4:C6, etc until all is done as it was in the previous problem, I now have to do the sum of (A3,B2,C1) for the first block, then sum of (D3,E2,F1) for the next, then sum of (A6, B5, C4), and then sum of (D6, E5, F4). Again this is just quick reference, I attached a new dummy file that shows what the outcomes should be.

Just like before, I want the user to be able to highlight all the data that needs to be calculated and have the results display a few rows below. The reason for this is that the data that I get can be hundreds of columns and rows wide and long and doing this manually is incredibly time consuming.

The dummy file has the macro that uses the previous method. I hope it can just be modified to make it easier for you guys. If not, I apologize, I'm just lost again.

Thank you so much for looking out for the little guys like me.

[Bob Phillips]

Here is a formula that does it

=SUMPRODUCT(--(N(OFFSET(INDEX($A$1:$AK$13,(ROW(A1)-1)*3+1,(COLUMN(A1)-1)*3+1),{3,2,1},{1,2,3},3,3))))

[Idiot]

Here is a formula that does it

=SUMPRODUCT(--(N(OFFSET(INDEX($A$1:$AK$13,(ROW(A1)-1)*3+1,(COLUMN(A1)-1)*3+1),{3,2,1},{1,2,3},3,3))))

That's genius! thank you!

Any possible way to make that a macro? Don't get me wrong, this is amazingly helpful, people here are just macro happy and scared of formulas. Their preferred method is to highlight all the data (just the numbers, not the labels) and run a macro to have it display the results just a few rows below.

Sorry for all the trouble. This did teach me though.

[Bob Phillips]

And I worked so hard on that one

[vba]

Sub MonthlytoQua()
Dim a As Long, b As Long, c As Long
c = Selection(1).Column
r = Selection(1).Row
cls = Selection.Columns.Count
rws = Selection.Rows.Count

j = 1
For a = c To c + cls - 1 Step 3

i = 1
For b = r To r + rws - 1 Step 3

Cells(rws + i + 2, j + 1).Formula = "=SUM(" & Cells(b + 2, a).Address & "," & Cells(b + 1, a + 1).Address & "," & Cells(b, a + 2).Address & ")"
i = i + 1
Next b

j = j + 1
Next a
End Sub
[/vba]

[Idiot]

And I worked so hard on that one

[vba]

Sub MonthlytoQua()
Dim a As Long, b As Long, c As Long
c = Selection(1).Column
r = Selection(1).Row
cls = Selection.Columns.Count
rws = Selection.Rows.Count

j = 1
For a = c To c + cls - 1 Step 3

i = 1
For b = r To r + rws - 1 Step 3

Cells(rws + i + 2, j + 1).Formula = "=SUM(" & Cells(b + 2, a).Address & "," & Cells(b + 1, a + 1).Address & "," & Cells(b, a + 2).Address & ")"
i = i + 1
Next b

j = j + 1
Next a
End Sub
[/vba]

Sorry. I loved the formula, I DID! Sorry for the delay, I kept losing internet because my service likes to **** me off right now.

This is great. I did notice one thing though. I don't want to push you, but I did notice that if the data I wanted to highlight is like 300 rows down, the data won't output below the select. Instead it will output the number of rows selected, plus three. Is there a way I can get it to display below the selection no matter where it is on the sheet? If I'm being a pain let me know. I'm just not sure how to do it myself.

So far my work around is to paste the data in a new sheet and then highlight and then of course run macro, but would be easier if I could just have it display under the selection, even if the data starts at the 300th row.

Thank you for all your help!!!!

[Bob Phillips]

Not sure I fully understand, but is this what you mean?

[vba]

Sub MonthlyQuota()
Dim a As Long, b As Long, c As Long
Dim Results As Long
c = Selection(1).Column
r = Selection(1).Row
cls = Selection.Columns.Count
rws = Selection.Rows.Count

With Selection.Cells(rws, 1)

.Offset(1, 0).Resize(Int((rws + 2) / 3) + 2).EntireRow.Insert
Results = .Offset(1, 0).Row
End With
j = 1
For a = c To c + cls - 1 Step 3

i = 1
For b = r To r + rws - 1 Step 3

Cells(Results + i, j + 1).Formula = "=SUM(" & Cells(b + 2, a).Address & "," & Cells(b + 1, a + 1).Address & "," & Cells(b, a + 2).Address & ")"
i = i + 1
Next b

j = j + 1
Next a
End Sub
[/vba]

[Idiot]

Not sure I fully understand, but is this what you mean?

[vba]

Sub MonthlyQuota()
Dim a As Long, b As Long, c As Long
Dim Results As Long
c = Selection(1).Column
r = Selection(1).Row
cls = Selection.Columns.Count
rws = Selection.Rows.Count

With Selection.Cells(rws, 1)

.Offset(1, 0).Resize(Int((rws + 2) / 3) + 2).EntireRow.Insert
Results = .Offset(1, 0).Row
End With
j = 1
For a = c To c + cls - 1 Step 3

i = 1
For b = r To r + rws - 1 Step 3

Cells(Results + i, j + 1).Formula = "=SUM(" & Cells(b + 2, a).Address & "," & Cells(b + 1, a + 1).Address & "," & Cells(b, a + 2).Address & ")"
i = i + 1
Next b

j = j + 1
Next a
End Sub
[/vba]

That's exactly it,
THANK YOU SO MUCH