Solved: Different Functions In Same Subtotal Row

[shaggles]

Is it possible to create a row in which some columns are counted, some are sumed and some are averaged using the subtotal method? Or some other way?

[Bob Phillips]

Yes, it is simple, Just do it in Excel with the macro recorder on, that will show you.

[shaggles]

Excuse my ignorance but how would I do it in Excel? When I use the subtotal wizard it only allows me to enter one function at a time.

[Bob Phillips]

Do all of your traget columns with the one allowed function type, say SUM, and then edit the other columns.

Change

=SUBTOTAL(9,rng)

to

=SUBTOTAL(3,rng)

for count

[shaggles]

Sorry I;m still confused. I need to manually update every field that needs a different function? I have about 165 subtotal rows. This is what the macro recorder gives me when I update just the first one:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/27/2009 by User
'
'
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 8, 9, 10 _
, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30), Replace:=True _
, PageBreaks:=False, SummaryBelowData:=True
Range("G14").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
Range("H14").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
Range("I14").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
Range("J14").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
Range("K14").Select
End Sub

[shaggles]

OK. Forgive me for being so dense. I can use find and replace to change all the 9's to 3's.

[Bob Phillips]

Het Shaggles, the idea was to use the macro recorder to give you the basic code. It should have been easy from there to make the changes, no need for Find and replace.

[shaggles]

I don't understand. How can I change the subtotal formula for on certain columns without either doing a find and replace or changing each cell manually?

[Bob Phillips]

Sub Macro1()
Selection.Subtotal GroupBy:=3, Function:=xlSum, _
TotalList:=Array(7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, _
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("G14").FormulaR1C1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
Range("H14").FormulaR1C1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
Range("I14").FormulaR1C1 = "=SUBTOTAL(9,R[-12]C:R[-1]C)"
Range("J14").FormulaR1C1 = "=SUBTOTAL(9,R[-12]C:R[-1]C)"
End Sub

[shaggles]

Sorry xld. I'm still not getting it. It looks like I would have to explicitely code every cell I want to change but that doesn't sound right so it must just be my lack of understanding. The find and replace thing is working so I'm going to mark this as solved. Thanks very much for your help. I'd never have gotten this far without it.

[Bob Phillips]

What I am saying is that you could have code that creates the subtotals and more code that does a find and replace, but that is not the 'proper' way to do it in code IMO.

You know what columns that you want as count not sum, and presumably the subtotal rows are variable, so you could write a simple Find/FindNext loop looking for the text 'Total' in column A (presumably) and chan ge the formula in the approipriate columns.

Not a big deal, just pointing out the alternatives.

[shaggles]

Thanks for your help. I'll try that.

[mikerickson]

If you need change functions regularly, you could use a cell to control which subtotal function you want

=SUBTOTAL(CHOOSE($A$1,3,9,8), someRange)

[shaggles]

OK. I was a little premature marking this solved. For some reason when I try to change the formula or replace the 9 with a 3 the range also changes for some (not all) of the formulas. I though maybe it was happening in the find and replace when there was a 9 somewhere in the range reference but that's not it. When I try to change the formula like below I get a circular reference. Probably not surprising to all of you but I really don't understand the R1C1 thing at all.

For Each e In xlw.worksheets(1).range("C1:C2500").cells
If e.Value Like ("*Total") Then
e.offset(0, 4).formular1c1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
e.offset(0, 5).formular1c1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
e.offset(0, 6).formular1c1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
e.offset(0, 7).formular1c1 = "=SUBTOTAL(3,R[-12]C:R[-1]C)"
End If
Next e

[Bob Phillips]

That isn't Find and Replace, that is just a simple insert. Where is Find and Replace?

It is also likely that not all formulae will go back 12 rows, it will vary.

[shaggles]

Sorry. This was the code I tried because the find and replace wasn't working. I don't understand the R1C1 at all so I just copied from your sample. This is the find and replace code I was using.

Set e = xlw.worksheets(1).columns("G:J")
e.Replace what:="9", replacement:="3"

It seems to change the range argument in unexpected (at least to me) ways in addition to changing the formula type.

[Bob Phillips]

How about trying



Set e = xlw.worksheets(1).columns("G:J")
e.Replace what:="SUBTOTAL(9", replacement:="SUBTOTAL(3"

[shaggles]

That seems like it did the trick. Thanks again xld. You're the best.