Subtotal and Count in Same Row Macro

Question

I have a report that I am trying to generate that shows loans made outside of policy, grouped by loan officer, with subtotals of dollar amounts of loans made outside of policy. I would also like to have a count of the number of loans made, but I cannot get the count to appear on the same line as the subtotal.

 Range("A2:K2", ActiveCell.End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1, 7, 8), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.CurrentRegion.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True

This results in the subtotals that I want in columns 1, 7 and 8, but a line is inserted above the subtotals and the count is presented in column 4 one row above the subtotals. I have tried different variations in the true/false statements at the end. I also tried to add a "dummy" column of 1s next to each loan, but I then want to hide the "dummy" column so the report will fit on one sheet of paper, and I don't know how to offset the resulting subtotal count, since the number of loans made outside of policy by various loan officers will vary between periods. Is there a way to do this?

Thanks, but how do I do that? From what I have read on it over the weekend, I would need something similar to: `Dim rng As Range Set rng = ("D3:D35565") For Each cCell In rng If rng.Cells.Formula Like "SUBTOTAL(9,*:*)" Then ActiveCell.Formula = SUBTOTAL(2,*:*) End If Next cCell` However, I don't have the syntax correct. Is this how I should approach the problem? (The compiler specifically does not like the wildcards in the `SUBTOTAL(2,*:*)` expression, but I only want to change the `SUBTOTAL(9` part, like you said. — Henchman21, Aug 18 '14 at 14:50
That's it! I needed to use subtotal(3 instead, but it worked! I can't see where I can upvote @pnuts, or I might not have enough reputation, but I want anyone looking for this question to know that this is the answer! — Henchman21, Aug 20 '14 at 14:42
If you get around to answering, I will select it as the correct one. In the meantime, I have cited and posted your work below in case others are looking for the same thing. — Henchman21, Aug 20 '14 at 16:47

score 2 · Accepted Answer · edited Feb 24 '15 at 10:30

As pnuts solved in the comments above, for my report to have the subtotals in the appropriate columns (1, 7 and 8 in this case) and the count in the appropriate column (column 4 in this case), the following code works beautifully:

Range("A2:K2", ActiveCell.End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1, 7, 8), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns(4).Replace What:="subtotal(9", Replacement:="subtotal(3", LookAt:=xlPart

Remember that if you want to count ALL the cells in a range, use the CountA function: "subtotal(3". If you are only requiring a count of cells that contain numbers (e.g., excluding logical values, text, error values, etc.,) then use the Count function: "subtotal(2".

Thanks again to pnuts for giving the correct answer!

Subtotal and Count in Same Row Macro

1 Answers1