Solved

Subtotal VBA not working as expected

Posted on 2013-12-19
7
221 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
  • 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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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 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…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 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

22 Experts available now in Live!

Get 1:1 Help Now