+ Reply to Thread
Results 1 to 2 of 2

VBA - add subtotal function except values that match criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    Ontario, Canada
    MS-Off Ver
    2013
    Posts
    15

    VBA - add subtotal function except values that match criteria

    Hello, Gang
    I have this code that is adding up values in column BA what ever can be grouped, based on Column U.
    I need to exclude red values from the subtotal
    Untitled.png

    Here is the beautiful code that I'm using to run the subtotals.
    I want this code to also exclude the red customs/brokerage/tax related values from the subtotal in column BA in yellow.
    Can't use any other cells except GREEN and YELLOW

    I can do that manually with AGGREGATE minus VLOOKUPs that match *tax*, *gst*, *brokerage*, *customs*,*return*, but I have a massive data table.
    =(AGGREGATE(9,4,range)-VLOOKUP("tax",range,2,FALSE)-VLOOKUP("*returns*",range,2,FALSE)-VLOOKUP("*customs*",range,2,FALSE)-VLOOKUP("*broker*",range,2,FALSE))

    Option Explicit
    
    Sub aSubTotal()
    Dim iCol As Integer
    Dim I As Integer
    Dim j As Integer
    
    Application.ScreenUpdating = False
    I = 2
    j = I
        'Loops through Col U Checking for match then when there is no match add Sum
        Do While Range("U" & I) <> ""
            If Range("U" & I) <> Range("U" & (I + 1)) Then
                Rows(I + 1).Insert
                Range("U" & (I + 1)) = "Subtotal " & Range("U" & I).Value
                For iCol = 53 To 53 'Columns to Sum i.e. column BA "Net Amount"
                    Cells(I + 1, iCol).Formula = "=SUM(R" & j & "C:R" & I & "C)"
                Next iCol
                Range(Cells(I + 1, 1), Cells(I + 1, 8)).EntireRow.Font.Bold = True
                Range(Cells(I + 1, 1), Cells(I + 1, 250)).Interior.Color = vbYellow
                I = I + 2
                j = I
            Else
                I = I + 1
            End If
        Loop
    Application.ScreenUpdating = True
    End Sub
    
    Sub Restore()
    'remove subtotals
       [a2:A5000].SpecialCells(4).EntireRow.Delete
    End Sub
    Thank you!
    Last edited by pkobzar; 12-20-2019 at 11:16 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,862

    Re: VBA - add subtotal function except values that match criteria

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Subtotal with index and match function
    By soreno in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-19-2019, 02:27 PM
  2. Replies: 1
    Last Post: 08-28-2019, 02:16 PM
  3. Replies: 10
    Last Post: 08-20-2018, 11:43 AM
  4. [SOLVED] Using Average with Subtotal function with criteria
    By Rianne in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-10-2017, 11:54 PM
  5. Index/Match Function for Finding Result in between Two Values with Two Criteria
    By KyleElliott in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2017, 01:19 AM
  6. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  7. SubTotal function. How do I keep reuse Subtotal criteria.
    By davidthegolfer in forum Excel General
    Replies: 0
    Last Post: 10-10-2006, 03:28 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1