Solved

Excel 2003 VBA Specify multiple column subtotals

Posted on 2011-09-08
8
1,053 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 250 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 9

Assisted Solution

by:chwong67
chwong67 earned 250 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

710 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