I've been waiting to see if my own question can be
answered and decided to answer yours.
Here is some code (this is written in Excel 2000 and I
don't have a copy of 97 to test it on.)
As for the code, it's not pretty. Just a quick solution.
To be able to make this work (note I'm basing this off of
Excel 2000's UI, so it might not work for you):
You will need to make visible the "Control Toolbox"
command bar. Can do this by right clicking anywhere on the
top tool bars, and selecting "Control Toolbox"
Drag a CommandButton on to the worksheet somewhere.
Right Click it and select view code.
Copy all the code below to the editor over everything that
exists.
Make sure to change rEND to a row like row 5 in your first
picture.
For instance, if you wanted to test my code you could
change rEND to 9 (using the data on the first picture).
If you wanted, you could also make this code start in the
middle of your data by changing both rBEGIN and rEND.
(using first picture: rBEGIN = 5 rEND = 15)
Once you copied this code over, save it in the Visual
Basic Editor, and then you can click the button.
Hope this works in 97...
******* START CODE ***********************************
Option Explicit
Const TOTALPRICE = 1 'Columns data is in
Const PRICE = 2
Const LOCATION = 3
Const ACCOUNT = 4
Const rBEGIN = 2 'Begin Row of your data
Const rEND = 9 'End Row of your data
'YOU NEED TO CHANGE rEnd to be very last row
'a row with a , or . in account
Private Sub CommandButton1_Click()
Call doTotal(rBEGIN,rEND)
End Sub
Sub doTotal(bRow As Long, eRow As Long)
Dim rRow As Long 'Read Row
Dim thisAccount As String
Dim nextAccount As String
Dim startRow As Long
Dim endRow As Long
Dim Break As Boolean
Dim myFormula As String
Dim i As Long
startRow = bRow
thisAccount = Trim(CStr(Cells(bRow, ACCOUNT).Value))
Break = False
bRow = bRow + 1
For rRow = bRow To eRow
nextAccount = Trim(CStr(Cells(rRow, ACCOUNT).Value))
If thisAccount <> nextAccount Then
endRow = rRow - 1
myFormula = "=SUM(B" & startRow & ":B" & endRow & ")"
For i = startRow To endRow
Cells(i, TOTALPRICE).Formula = myFormula
Next i
Rows(rRow).Delete 'delete this row
startRow = rRow 'reset startrow for next account
thisAccount = Trim(CStr(Cells(rRow, ACCOUNT).Value))
rRow = rRow - 1 'decrement rRow for next iteration
eRow = eRow - 1 'one less row to iterate
End If
If rRow = eRow Then 'Prevent Infinite Loop
Exit For
End If
Next rRow
End Sub
******* END CODE ***********************************
