I have a sheet which I can sort and subtotal fine in VBA. Here is the code:
' Macro1 Macro
' Keyboard Shortcut: Ctrl+Shift+A
"December 12").Sort.SortFields.Add Key:=Range( _
"D2:D55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
The code above puts the count of the number of rows in the sub-total section in column D of the sub-total rows in the sheet for each subtotal section. This is what I want it to do. Now, on each sub-total row, there are three other columns that I want the code to calculate a number and place it at the foot of the sub-total columns just as it does for the one sub-total the code is already calculating.
Now that we have sub-total rows in the worksheet, I need to add to the code so that VBA will look for each sub-total row and run a COUNTIF formula on columns E, F, and H. For columns E and F, it is to COUNTIF the criteria is "Y", and for column H, the criteria is "N". Then it needs to put the result of the COUNTIF formulas on the sub-total row for each section of sub-totals. The number of rows in any given sub-total section is dynamic and can change any time the code is run. I just do not know how to have VBA locate only the rows in each sub-total section, find the sub-total rows, and run the COUNTIF formula on the proper column with the proper criteria.
After it completes the COUNTIF process, I need VBA to set each sub-total row, columns A through H, to be highlighted in yellow and boldfaced.
A copy of how the sheet is to look is attached.
Any help you can provide on this will be appreciated!