Custom Formula in Sub-Total

[ppmvisk]

I appreciate all I find in these forums and after searching for hours I don’t think I’ve found what I’m after completely this time but have found all the parts of the problem. (I’ll apologize in advance if I missed this answer.) So now, how to put it all together?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Simply put, the built-in function for sub-totals is what I’m after, but I need to define the specific formula used for each column selected, not just all sum or all average. The built-in function already finds the change in row (for example customer name), inserts the extra row for the sub-total line, creates the outline, and as an example, inserts the formula for sum as =SUBTOTAL(9,Gn:Gm) where n is the start of the variable row range and m is the end of that range. This repeats for each group and the value of n and m are reset in each sub-total as needed for the starting and ending row numbers in that group.
<o> </o>
How can I say that for column G, the formula is =SUBTOTAL(9,Gn:Gm) but for column I the formula is =(SUMPRODUCT(Gn:Gm*In:Im)/SUM(Gn:G4m))?
<o> </o>
There are additional columns that also require the moving average sub-total to show current pricing, and projecting a new price plan. The final formula then gives the change in percent of the two moving averages. Sticking with my convention above that would be (in cell AA) =(SUMPRODUCT(ABn:ABm*Gn:Gm)/SUMPRODUCT(In:Im*Gn:Gm))-1
<o> </o>
So I know the tool can generate the processing to handle variable row sets and find the range in each set inserting a formula in selected columns with references to that range. I know I can put in the formula for a weighted average subtotal and get the desired results. But need to do both at once. I looked at building the sub-total and recording it thinking I could change what it did – no luck. I looked at using the results of the built-in function but the ranges don’t seem accessible. Building a macro seems possible to do the same, but sounds unlikely I’m the first to need this functionality. Has anyone seen this built or know of an add-in providing the functionality?
<o> </o>
Thanks

 

[GlennUK]

Create a macro to create the subtotal, and then examine the results, and whereever a subtotal has been inserted ( look for a certain string, or a formula that begins with =SUBTOTAL ), then add your formula to that row. You can get the required range from the formula of the SUBTOTAL ... look at the Precedents property. E.g.

    For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("G:G"))
        If Left(c.FormulaR1C1, 4) = "=SUB" Then
            c.Offset(0, 2).Formula = "=(SUMPRODUCT(" & c.Precedents.Address & "*" & c.Precedents.Offset(0, 2).Address & ")/SUM(" & c.Precedents.Address & "))"
        End If
        Next

 

[ppmvisk]

Thank you! I was starting to think I stumped the experts out there. Yes, I get it - I was considering running the built-in function for subtotal and then using those results in the macro to "simply" replace the subtotal with the custom formula but that too has complexities to deal with. This looks like it does the trick I had in mind.

 

[GlennUK]

Glad to have been of some help.