[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Subtotal VBA not working as expected

Posted on 2013-12-19
7
Medium Priority
?
277 Views
Last Modified: 2014-09-05
Hi I have VBA to subtotal based on columns.  IF column D is Retail then I want to group (subtotal) on Column 6.  IF Column D is Market I want to group (subtotal) based on Column 5.   I just realized it not working as expected and wondering if it is because the Value in column D is the result of a VLOOKUP and if this the case how can I get around it.

Sub AddSubs2()
    
    Dim LM As Long, i As Long
      
    LM = Range("D" & Rows.Count).End(xlUp).Row
  
    
    For i = 1 To LM
    
    If Value = "Retail" Then
    Worksheets("To Open in '13").Activate
    Selection.Subtotal GroupBy:=6, Function:=xlAverage, TotalList:=Array(11, _
        12, 13, 16, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Else
       
    Worksheets("To Open in '13").Activate
    Selection.Subtotal GroupBy:=5, Function:=xlAverage, TotalList:=Array(11, _
        12, 13, 16, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
    End If
    
  Next i

    
 
End Sub

Open in new window

0
Comment
Question by:jmac001
[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
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39729940
That should not be a reason. I am sure there is some other reason. Would it be possible to post a small(fake) sample file showing this problem?

Where is the value of "Value" calculated?

You can also reduce your code to

Sub AddSubs2()
    Dim LM As Long, i As Long
    LM = Range("D" & Rows.count).End(xlUp).Row
    For i = 1 To LM
        Worksheets("To Open in '13").Activate
            Selection.Subtotal GroupBy:=IIf(Value = "Retail", 6, 5), Function:=xlAverage, TotalList:=Array(11, _
                12, 13, 16, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Next i
End Sub
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39730856
No way to tell if you don't define 'Selection'. There's no need to select anything in code. Having a formula in a range to subtotal won't  affect it.

Zack Barresse
0
 

Author Comment

by:jmac001
ID: 39736403
Still was unable to get the VBA to work attaching a sample file. The file includes the results that I am getting, what I would like it to look like, the orginal format and the source data.
EE-Sample-Variance-12.20.xlsm
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Accepted Solution

by:
Zack Barresse earned 2000 total points
ID: 39736856
Let's define this logic. Your file has subtotaled column E, with header 'Partner'. Is that right? Looks like you did for every change in column D, labeled 'Type'. So this basically says, for every change in column 'Type' add a subtotal to column 'Partner' and use the function 'Average'. I'm assuming that's not what you're after.

As for your 'WouldLikeExample' sheet, it looks like you want a subtotal in column K, with header 'GSF', for every change in column E with header 'Partner' and where column D with header 'Type' equals "Retail".

The way Subtotal works you can't exclude specific values from a column to look at the change in. So wanting only "Retail" in column D can't be done with the native Subtotal functionality. You can add a Subtotal to column E and then add another Subtotal to column D (you'd have to make sure you uncheck the Replace current subtotals checkbox). But this would add a subtotal for every change in column D, not just the cells with "Retail". You could add both subtotals and then just hide the undesired rows.

Why would you not use a PivotTable to get the subtotals you need here? This is a fairly specific request.

Also, by default the Subtotal functionality will add the "Column Average", where "Column" is your column header, into the column of change. This is different than what you have as your 'WouldLikeExample'.

If you wanted to use the built-in functionality of Subtotal, you could just hide all rows in column D which aren't a subtotal of Retail, in which you could use something like this...

Option Explicit

Sub AddSubtotals()
    Dim WKS                     As Worksheet
    Dim rData                   As Range
    Dim iLastRow                As Long
    Dim iRow                    As Long
    Dim bTEST                   As Boolean

    Set WKS = ActiveSheet    'set as desired
    iLastRow = WKS.Cells(WKS.Rows.Count, "E").End(xlUp).Row
    Set rData = WKS.Range("A14:R" & iLastRow)

    Call SubtotalRange(rData, , , , , , , True)
    Call RangeSort(rData, 4, 5)
    Call SubtotalRange(rData, 5, xlAverage, 11, True, False, True, False)
    Call SubtotalRange(rData, 4, xlAverage, 11, False, False, False, False)
    For iRow = 1 To rData.Rows.Count
        If rData(iRow, 4).Value Like "* Average" And rData(iRow, 11).Formula Like "=SUBTOTAL(*" And rData(iRow, 4).Value <> "Retail Average" Then
            rData(iRow, 1).EntireRow.Hidden = True
        End If
    Next iRow
End Sub

Sub RangeSort(ByVal SortRange As Range, _
              ParamArray ColumnToSort() As Variant)
    Dim WKS                     As Worksheet
    Dim iArrayStep              As Long
    If IsEmpty(ColumnToSort) Then Exit Sub
    Set WKS = SortRange.Parent
    WKS.Sort.SortFields.Clear
    For iArrayStep = LBound(ColumnToSort) To UBound(ColumnToSort)
        WKS.Sort.SortFields.Add Key:=Intersect(SortRange, SortRange(1, ColumnToSort(iArrayStep)).EntireColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Next iArrayStep
    WKS.Sort.SetRange SortRange
    WKS.Sort.Header = xlYes
    WKS.Sort.MatchCase = False
    WKS.Sort.Orientation = xlTopToBottom
    WKS.Sort.SortMethod = xlPinYin
    WKS.Sort.Apply
End Sub

Sub SubtotalRange(ByVal rngData As Range, _
                  Optional ByVal ColumnToGroupBy As Long, _
                  Optional ByVal FunctionToUse As Variant, _
                  Optional ByVal ColumnToSubtotal As Long, _
                  Optional ByVal ReplaceSubtotals As Boolean, _
                  Optional ByVal AddPageBreaks As Boolean = False, _
                  Optional ByVal SummarizeBelow As Boolean = True, _
                  Optional ByVal RemoveSubtotals As Boolean = False)
    On Error Resume Next
    If RemoveSubtotals Then
        rngData.RemoveSubtotal
    Else
        rngData.Subtotal GroupBy:=ColumnToGroupBy, Function:=FunctionToUse, TotalList:=Array(ColumnToSubtotal), Replace:=ReplaceSubtotals, PageBreaks:=AddPageBreaks, SummaryBelowData:=SummarizeBelow
    End If
    On Error GoTo 0
End Sub

Open in new window


Zack
0
 

Author Comment

by:jmac001
ID: 39746931
Hi Zack,

Is it possible to do two subtotals based on the data in the table? I did this in the "Wouldlike" tab by copying and inserting the header row where there was a change in the "Type" column. However this is a little time consuming and  I consistantly have to update this data and thought it would be easier in VBA.  

I have VBA that sorts the data to the point that you see it in the To Open in '13 tab so the so the first subtotal I need is on Type = Market and for every change in Partner show the average for columns K,L,M,P and Q.  

The second subtotal is Type = Retail, for every change in Shopfitter show the averages in columns K,L,M,P and Q.

If this can be done another way that will allow me to save time I would be happy to consider as well.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39790061
Hi, sorry for the absence, I didn't see the response and got lost in the email shuffle, apologies.

While you can do multiple subtotals, it's limited to the functionality shown earlier, which I don't think is quite what you're looking for. Because of that I don't think you'll want to use the native functionality. This means we would need to build out the whole thing using VBA, probably involving a loop, to do it all, which we can do if you'd like. Do you still need a solution for this?

Zack
0
 

Author Comment

by:jmac001
ID: 39795409
Hi Zack,

Thanks for responding back, yes I do still need a solution.  Please let me know if you need any additional info. The workbook that I attached has all of the VBA that I am using currently. Let me know if you need anything else.
0

Featured Post

Technology Partners: 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

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 article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

649 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