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!
Bookmarks