Having problem with subtotal helpme out

[ramse]

Hi ,


this is the code i'm using to do summary dynamically because i dont
know no of rows. It will changes every day. I'm facing the problem
with this code. pls help me out.
sttype hr1 hr2
------ -- ---
S7 10 20
S7 30 30
S14 40 30
S14 20 50

OUTPUT. in the last row looks like this.
S7 40 50
S14 60 80.

code:
-----
Sub summary()
Dim srow As Integer
Dim erow As Integer
Dim STYPE As String
Dim STVALUE As String
Dim LROW As Integer
Dim nrow As Integer
Dim LCONTINUE As Boolean


LROW = 0
nrow = 1
Range("a2").Select
LCONTINUE = True

Do Until Selection.Value = ""
Selection.Offset(1, 0).Select
Loop
endrow = Selection.Row - 1
LROW = LROW + 1
nrow = nrow + 1


STYPE = "A2"
STVALUE = "A" & CStr(LROW)


For lcount = 1 To endrow
'While LCONTINUE = True
If Range(STYPE).Value <> Range(STVALUE).Value Then
Range("b" & endrow + 2).Formula = "=SUM(b2:b" & endrow & ")"
Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight
STYPE = "A" & CStr(nrow)
End If
'Wend
Next lcount
End Sub



your help is greatly appreciated.

Thanks,
Ramana.

 

[Lonnie M.]

Hi, I am not sure if I understand your question, but here is my stab at
it:

Sub mySubtotal
Dim endrow&
endrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Cells(endrow + 2, 2).Formula = "=SUBTOTAL(9,b2:b" & endrow & ")"
' SUBTOTAL(109, ... is a better option if you are using Excel 2003
Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight
End Sub

HTH--Lonnie M.

 

[Dave Peterson]

Wouldn't it be easier to use Data|subtotals?

Hi ,

this is the code i'm using to do summary dynamically because i dont
know no of rows. It will changes every day. I'm facing the problem
with this code. pls help me out.
sttype hr1 hr2
------ -- ---
S7 10 20
S7 30 30
S14 40 30
S14 20 50

OUTPUT. in the last row looks like this.
S7 40 50
S14 60 80.

code:
-----
Sub summary()
Dim srow As Integer
Dim erow As Integer
Dim STYPE As String
Dim STVALUE As String
Dim LROW As Integer
Dim nrow As Integer
Dim LCONTINUE As Boolean

LROW = 0
nrow = 1
Range("a2").Select
LCONTINUE = True

Do Until Selection.Value = ""
Selection.Offset(1, 0).Select
Loop
endrow = Selection.Row - 1
LROW = LROW + 1
nrow = nrow + 1

STYPE = "A2"
STVALUE = "A" & CStr(LROW)

For lcount = 1 To endrow
'While LCONTINUE = True
If Range(STYPE).Value <> Range(STVALUE).Value Then
Range("b" & endrow + 2).Formula = "=SUM(b2:b" & endrow & ")"
Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight
STYPE = "A" & CStr(nrow)
End If
'Wend
Next lcount
End Sub

your help is greatly appreciated.

Thanks,
Ramana.