subtotal question
 

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

subtotal question
 

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