Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2003 VBA Specify multiple column subtotals

Posted on 2011-09-08
8
Medium Priority
?
1,151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 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