Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2003 VBA Specify multiple column subtotals

Posted on 2011-09-08
8
Medium Priority
?
1,209 Views
Last Modified: 2012-06-21
I have a worksheet containing 4 columns.  I want to use the subtotal method of the range object.  The third and fourth columns contain numeric data.  I want the sum of each group for the 3rd column data and the max of each group for the 4th column data.  Does anyone have have a VBA code snippet to accomplish this?

A     B     C     D
x      x     1     4
x      x     2     3
x      x     3     2
x      x     4     1
------------------
Subtotal: 10   4
0
Comment
Question by:irc200
  • 3
  • 3
8 Comments
 
LVL 6

Accepted Solution

by:
TinTombStone earned 1000 total points
ID: 36503028
Provided your data is as your example.  (number of rows not a problem)

then:
Sub TotCols()

Range("A1").Offset(Range("A1").CurrentRegion.Rows.Count, 2).FormulaR1C1 = "=subtotal(9,R[-" & Range("A1").CurrentRegion.Rows.Count & "]C:R[-1]C)"

Range("A1").Offset(Range("A1").CurrentRegion.Rows.Count - 1, 3).FormulaR1C1 = "=max(R[-" & Range("A1").CurrentRegion.Rows.Count - 1 & "]C:R[-1]C)"

End Sub

Open in new window

0
 

Author Comment

by:irc200
ID: 36503222
Thanks TinTombStone.  Unfortunately my data is a little more complicated than the example. There are multiple groups.  Here is an updated example:  Sorry that I did not give a proper example.  

A     B        C     D
z      x        1     4
z      x        2     3
z      x        3     2
z      x        4     1
---------------------
Subtotal x: 10   4
z      y        6     9
z      y        7     8
z      y        8     6
z      y        9     7
---------------------
Subtotal y:  30   9

GrandTotal Z: 40  9
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 36503779
Hoping someone can pick this up from here

I will be away till Monday
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Assisted Solution

by:chwong67
chwong67 earned 1000 total points
ID: 36509024
Assume the sheet as below and try the code:
A     B        C     D
z      x        1     4
z      x        2     3
z      x        3     2
z      x        4     1
z      y        6     9
z      y        7     8
z      y        8     6
z      y        9     7

Sub SubTot()
Dim x As Long
'Set Rng = Range("A1:A" & UsedRange.Rows.Count)
a = 2
b = 2
For i = 2 To ActiveSheet.Rows.Count
    If Range("A" & i).Value = "" Then Exit For
    If Range("A" & i).Value <> Range("A" & i + 1).Value Or _
       Range("B" & i).Value <> Range("B" & i + 1).Value Then
       Rows(i + 1 & ":" & i + 3).Select
       Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       Range("A" & i + 1).Value = "----------"
       Range("A" & i + 2).Value = "Subtotal " & Range("B" & i).Value
       Range("C" & i + 2).Value = "=SUM(C" & b & ":C" & i & ")"
       Range("D" & i + 2).Value = "=MAX(D" & b & ":D" & i & ")"
       
       If Range("A" & i).Value <> Range("A" & i + 4).Value Then
            Rows(i + 4 & ":" & i + 6).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("A" & i + 4).Value = "----------"
            Range("A" & i + 5).Value = "total " & Range("A" & i).Value
            Range("C" & i + 5).Value = "=SUM(C" & a & ":C" & i + 2 & ")/2"
            Range("D" & i + 5).Value = "=MAX(D" & a & ":D" & i & ")"
            Range("A" & i + 6).Value = "----------"
            i = i + 6
            a = i + 1
            b = i + 1
       Else
            i = i + 3
            b = i + 1
       End If
    End If
Next i

Open in new window

0
 

Assisted Solution

by:irc200
irc200 earned 0 total points
ID: 36510437
Thanks for your help chwong67.   Your solution worked for the example.  But I realized that the data was not always consistent in the number of rows for each subtotal group.

 In the meantime I came up with a more generalized solution.  I used the subtotal method of the range object for column C.  For Column D I copied the subtotal formula from column C and changed the first argument from 9 to 4.
 e.g. Cell C5 formula:  =Subtotal(9,C1:C4)
        Cell D5 formula:  =Subtotal(4,D1:D4)
0
 

Author Comment

by:irc200
ID: 36519798
I've requested that this question be closed as follows:

Accepted answer: 0 points for irc200's comment http:/Q_27297615.html#36510437

for the following reason:

My solution solved my specific instance of the problem
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 36519799
irc200's  question ended with  "Does anyone have have a VBA code snippet to accomplish this"

As irc200's solution does not seem to be a VBA solution, it can not answer the question
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question