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

--


Gary

maybe...

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

Gary Keramidas wrote:

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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary


"Dave Peterson" wrote in message
...
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.

Gary Keramidas wrote:

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

--

Gary

--

Dave Peterson



--

Dave Peterson