Ramana,
You seem desperate and maybe I'm feeling foolish today so I'm giving you the
code. If this is part of an independent learning experience or you are a
student, then I've done a good deed. However if you have a job and need
this to please your boss then I feel I've done you a disservice. You should
really tell your boss that you don't know how to solve the problem and that
he should anticipate "urgent" situations better and either provide more
training for you or find someone else with the right skills. There's no use
in setting false expectations.
My solution assumes your data matrix starts at the upper left cell and is
terminated by a blank row. Also, I think your solution of trying to stick
formulas into the worksheet was the wrong approach. Hopefully you'll be
able to understand this code sufficiently to see that I took a different
approach.
Sub subTotal()
Dim s7Sub(1) As Long
Dim s14Sub(1) As Long
Dim currentRow As Integer
Dim tag As String
Erase s7Sub
Erase s14Sub
currentRow = 1
tag = Trim(ActiveSheet.Cells(currentRow, 1))
While tag <> ""
If tag = "S7" Then
s7Sub(0) = s7Sub(0) + ActiveSheet.Cells(currentRow, 2)
s7Sub(1) = s7Sub(1) + ActiveSheet.Cells(currentRow, 3)
ElseIf tag = "S14" Then
s14Sub(0) = s14Sub(0) + ActiveSheet.Cells(currentRow, 2)
s14Sub(1) = s14Sub(1) + ActiveSheet.Cells(currentRow, 3)
End If
currentRow = currentRow + 1
tag = Trim(ActiveSheet.Cells(currentRow, 1))
Wend
currentRow = currentRow + 1
ActiveSheet.Cells(currentRow, 1) = "S7 subtotal"
ActiveSheet.Cells(currentRow, 2) = s7Sub(0)
ActiveSheet.Cells(currentRow, 3) = s7Sub(1)
currentRow = currentRow + 1
ActiveSheet.Cells(currentRow, 1) = "S14 subtotal"
ActiveSheet.Cells(currentRow, 2) = s14Sub(0)
ActiveSheet.Cells(currentRow, 3) = s14Sub(1)
End Sub