subtotal question

  • Thread starter Thread starter Gary Keramidas
  • Start date Start date
G

Gary Keramidas

is there any easier way to use the subtotal property to get the count and
the sum of each group? it seems to only allow one or the other.
..Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8)

so i adapted some code from the find help file to do this, but was wondering
if there was a better way.


Sub AddCount()
Dim cell As Range
Dim firstaddress As String
Dim c As Range
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
With Worksheets("sheet1").Range("i4:i" & lastrow)
Set c = .Find("subtotal", LookIn:=xlFormulas)
If Not c Is Nothing Then
firstaddress = c.Address

Do
c.Offset(0, -4).Formula = Left(c.Formula, 10) & "3," _
& Right(c.Formula, Len(c.Formula) - 12)
c.Font.Bold = True
c.Offset(0, -4).Font.Bold = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

End Sub
 
You could just apply another data|subtotals (and don't replace current
subtotals).

The first would use Sum; the second Count.

Another option would be to use a second column that's always populated and use
Sum for the Data|subtotals. But instead of using .Find's to fix up the
=subtotal() formulas, you could just edit|replace with that column selected.
Well, as long as you don't have =subtotal() formulas in the original range.

Or maybe a pivottable would do what you want.
 
thanks, added this and it seems to work fine.
..Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(3), Replace:=False

i did get to practice some code manipulation, though<g>
 
after i put the 2 subtotal line together, something weird happened.

i sort by the persons name and then get a total and count.
for every person except the last person, it lists the total line and then
the count line right below it.
for the last person, it reverses the 2 lines, the count is first and the
total line is right below it.
since these 2 lines are consecutive in the code, i don't think the code is
affecting it.


..Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), Replace:=False
..Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(3), Replace:=False

not a big deal, just wondering

--


Gary


Gary Keramidas said:
thanks, added this and it seems to work fine.
.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(3),
Replace:=False

i did get to practice some code manipulation, though<g>
Click to expand...
 
maybe...

Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;831824

Gary said:
after i put the 2 subtotal line together, something weird happened.

i sort by the persons name and then get a total and count.
for every person except the last person, it lists the total line and then
the count line right below it.
for the last person, it reverses the 2 lines, the count is first and the
total line is right below it.
since these 2 lines are consecutive in the code, i don't think the code is
affecting it.

.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), Replace:=False
.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(3), Replace:=False

not a big deal, just wondering
Click to expand...
 
Back
Top