75177

VBA Excel - Inserting Subtotal Formulas into Specific Rows

Question:

My issue is kind of a blend of what's happening <a href="https://stackoverflow.com/questions/25739774/vba-excel-summing-a-range-of-cells-based-on-result-in-a-different-range-of-cel" rel="nofollow">here</a> and <a href="https://stackoverflow.com/questions/22610818/how-to-add-groups-based-on-conditions-in-excel" rel="nofollow">here</a>, but is very much neither. I've got a weird dataset that is structured more like a hierarchy than a pure series of data points. Something like this:

<strong>Item Budget Sales</strong> GROUP - Cats 0 120 FY 13 Persian 0 0 FY 13 Maine Coon 12 0 FY 14 Maine Coon 50 0 FY 12 Tabby 1 0 FY 13 Tabby 1 0 FY 14 Tabby 2 0 FY 14 Alley 12 0 GROUP - Dogs 0 201 FY 14 Collie 20 0 FY 14 Lab 31 0 FY 13 Golden Retriever 12 0 FY 12 Golden Retriever 0 0 GROUP - Gold Fish 0 50 FY 14 Goldfish 100 0 FY 13 Clown Fish 20 0 Tanks Fees 150 0

I need a macro that can neatly identify the GROUP lines and then sum the group underneath it -- without capture the next group. In other words, I need the cat budget line to sum only the cat budgets.

So the macro would need to identify the line with the "GROUP*", then search down until it finds the next "GROUP*", and sum the space in between Cats and Dogs -- preferably as a function -- on the "GROUP - Cats" line.

I know this has to be possible, but I'm worried it's too complicated for my basic abilities in VBA.

<strong>EDIT:</strong> The end product would be a formula in the Budget column that is simply =SUM(B3:B9). Then B10 (the Dogs GROUP) would have a formula such as =SUM(B11:B14). For Gold Fish: =SUM(B16:18).

But since each my dataset is always changing (for instance, this week there may be 20 lines in the Cats section instead of 18 the previous week), I need a macro that can find the space between the GROUP lines.

<strong>EDIT 2:</strong> The VBA I'm using currently does something similar to what I'm looking for -- it essentially groups and collapses my sections based on the numbers that appear in the Sales column:

Dim rStart As Range, r As Range Dim lLastRow As Long, sColumn As String Dim rColumn As Range '### The Kittens everywhere! thing is just to make sure the last group has an end point Range("C1").End(xlDown).Offset(1).Value = "Kittens everywhere!" sColumn = "C" With ActiveSheet.Outline .AutomaticStyles = False .SummaryRow = xlAbove .SummaryColumn = xlRight lLastRow = Cells(Rows.Count, sColumn).End(xlUp).Row With ActiveSheet Set rColumn = .Range(.Cells(1, sColumn), Cells(lLastRow, sColumn)) With rColumn Set r = .Cells(1, 1) Do Until r.Row > lLastRow If rStart Is Nothing Then If r.Value = "0" Then Set rStart = r End If Else If r.Value <> "0" Then Range(rStart, r.Offset(-1, 0)).Rows.Group Set rStart = Nothing End If End If Set r = r.Offset(1, 0) Loop End With End With ActiveSheet.Outline.ShowLevels RowLevels:=1 End With Range("C:C").Find("Kittens everywhere!").ClearContents

There's more to the macro -- because it's also doing some things like highlighting the GROUP rows -- but I'm sure if I can jam this SUM function stuff into that section or not.

Answer1:

This is a suggestion that may or may not be appropriate but it does offer an option for you. It uses the built in Excel SubTotal function. There are some pros and cons (see below).

It requires two Subs, one to apply SubTotals:

Sub STPets() Dim ws As Worksheet Dim strow As Long, endrow As Long, stcol As Long, endcol As Long Dim drng As Range strow = 3 stcol = 3 'Col C endcol = 6 'Col F Set ws = Sheets("Sheet1") With ws 'find last data row endrow = Cells(Rows.Count, stcol).End(xlUp).Row 'sort data Set drng = .Range(.Cells(strow, stcol), .Cells(endrow, endcol)) drng.Sort Key1:=.Cells(strow + 1, stcol), Order1:=xlAscending, Header:=xlYes 'apply Excel SubTotal function .Cells.RemoveSubtotal drng.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3) End With End Sub

and one to Clear e.g. to add more data:

Sub RemoveSTPets() Dim ws As Worksheet Set ws = Sheets("Sheet1") ws.Cells.RemoveSubtotal End Sub

The data can be totalled and cleared at will, by using two buttons assigned to these macros:

<img alt="Apply SubTotals" class="b-lazy" data-src="https://i.stack.imgur.com/nyuix.png" data-original="https://i.stack.imgur.com/nyuix.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

<img alt="Remove SubTotals" class="b-lazy" data-src="https://i.stack.imgur.com/61ELv.png" data-original="https://i.stack.imgur.com/61ELv.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

As you can see it requires a slight re-arrangement of your data but arguably this makes it more flexible and consistent with database format (looking to the future?). It is also arguably easier to add new data anywhere in the list and easier to add/change groups (Codes) i.e. Clear > Add more data > re-Total. You can customise further in accordance with the Excel SubTotal functionality.

Finally, at the risk of overstepping the brief, but further food for thought perhaps - it might also be a good idea to separate-out your 'FY 13' and 'FY 14' identifiers into a separate "FY" column. You may then find it more flexible to do further analysis on your data over time.

Recommend

  • Assigning fixed drive letters to USB Devices on Windows Server (2012)
  • Writing a VBS file using batch
  • Unable to increase heap size for JMeter on Mac OSX
  • PHPExcel excel read is not working for some cells with calculation
  • Excel/VBA - Loop through range
  • Remove duplicates based on order
  • Excel 2010 search for text in IF function - separate cell data
  • not a valid l-value - verilog compiler error
  • draw9patch just hangs on Mac 10.7.5
  • Why is the return value of Perl's system not what I expect?
  • function cannot execute on segment because it accesses relation
  • AngularJs ng-repeat filtering by a deeper tier of data
  • Is it possible to disable certain weekdays in DatePickerDialog?
  • Get or convert Week of year to ISO week
  • Keep transition effect on 1 div from moving the div that follows?
  • Jquery Show & ScrollTop (or ScrollTo)
  • Struct pointer casts
  • UICollectionView in UITableview - Get tag of tapped UICollectionView
  • Initialization section of the package
  • Layout design help Android
  • Custom WebViewPage inject code when razor template is rendering
  • Find VMID for running instance
  • jQuery - resize an elements height to match window without refreshing, on window resize
  • Accessing Rows In A LINQ Result Without A Foreach Loop?
  • Primefaces lazy datascroller calling load twice
  • iText RadioGroup/RadioButtons across multiple PdfPCells
  • Aptana 3 remove bundle (jquery)
  • How to access meteor package name inside package?
  • How do I retrieve the user information of a user authenticated with Apache's mod_ldap?
  • Rest Services conventions
  • SonarQube: Cannot deactivate rule with missing quality profile
  • Uncaught TypeError: $(…).select2 is not a function
  • Clarification on min distance on LocationManager.requestLocationUpdates method, min Distance paramet
  • Exception “firebase.functions() takes … no argument …” when specifying a region for a Cloud Function
  • Highlight one bar in a series in highcharts?
  • SSO with signing and signature validation doesn't work
  • ActionScript 2 vs ActionScript 3 performance
  • Calling of Constructors in a Java
  • PHP: When would you need the self:: keyword?
  • json Serialization in asp