Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I have a spreadsheet that I need to add subtotals. I have no problem creating the first set of subtotals in VBA. But I have not been able to figure out how to create subtotals within subtotals.

    I am probably not explaining this very well, so am attaching a sample spreadsheet. The first tab has the original raw data, and the second shows the subtotals as they should be. I know I can record a macro, but the problem is that the number of rows within each group will change from month to month, which blows up the recorded macro.

    Any help is greatly appreciated.

    Ken
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the Macro Excel recorded when I did the double subtotaling.

    Code:
    Sub DubSub()
    
    '
    	Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _
    		7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    	Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _
    		7), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    End Sub
    There is no reference to a specific range. As long as your data is continuous and you have a cell within your table selected, you can use the recorded macro.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    That worked. However, if there is only one group and subgroup in the first group it puts the column one totals first, and then the second column subtotals after it.

    See attached for results when above condition exists.

    Thanks.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    As usual, you were dead right. Editing the registry fixed it.

    Thanks again!

    [quote name='HansV' post='776137' date='20-May-2009 11:03']It works correctly for me in Excel 2002 SP3.
    See if Multilevel subtotals are in the wrong position in Excel 2002, in Excel 2003, and in Excel 2007 helps.[/quote]

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Using the same spreadsheet as an example, I would like to find the cell with first occurrence and last occurrence of a name, or in this case Region. Based on the first and last location, I want to take some action for cells in between them.

    I know I can create variables for each, (i.e. FirstCell, LastCell), but I don't know how to put the values of the cells into the variables. It should be simple, but I am having the forest and trees syndrome again.

    Any help is greatly appreciated.

    Ken

    [quote name='HansV' post='776137' date='20-May-2009 11:03']It works correctly for me in Excel 2002 SP3.
    See if Multilevel subtotals are in the wrong position in Excel 2002, in Excel 2003, and in Excel 2007 helps.[/quote]

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I assumed you wanted to run the code after the subtotals have been added.

    [codebox]
    Sub groups()
    Dim cFirst As Long, cLast As Long, i As Long
    i = 2
    cFirst = 2

    Do While Cells(i, 3) <> ""
    If InStr(Cells(i, 2), "Total") > 0 Then
    cLast = i - 1
    With Range(Cells(cFirst, 2), Cells(cLast, 2)).Interior 'replace with your actions
    .ColorIndex = i Mod 54 'replace with your actions
    End With 'replace with your actions
    cFirst = cLast + 2
    i = i + 1
    Else
    i = i + 1
    End If
    Loop
    End Sub
    [/codebox]

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks! I took your suggestion, and combined some of Hans' oCell code got it working perfectly.

    THANKS!

    [quote name='mbarron' post='776237' date='20-May-2009 21:54']I assumed you wanted to run the code after the subtotals have been added.

    [codebox]
    Sub groups()
    Dim cFirst As Long, cLast As Long, i As Long
    i = 2
    cFirst = 2

    Do While Cells(i, 3) <> ""
    If InStr(Cells(i, 2), "Total") > 0 Then
    cLast = i - 1
    With Range(Cells(cFirst, 2), Cells(cLast, 2)).Interior 'replace with your actions
    .ColorIndex = i Mod 54 'replace with your actions
    End With 'replace with your actions
    cFirst = cLast + 2
    i = i + 1
    Else
    i = i + 1
    End If
    Loop
    End Sub
    [/codebox][/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •