Solved

Subtotal VBA not working as expected

Posted on 2013-12-19
7
254 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
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 14

Accepted Solution

by:
Zack Barresse earned 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

717 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