Solved

Excel 2003 VBA Specify multiple column subtotals

Posted on 2011-09-08
8
952 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now