Subtotals

[Michael Thompson]

The following is from a single text file that contains several
batches.
Each batch is seperated by the Batch Delimiter.
Each batch can have multiple 6 records (first field), but only one of
all the other records (1,5,8,9).

My Problem:
The third field of the 6 record is totaled within each batch and that
amount is inserted into position 4 of the 8 record and position 5 of
the 9 record.
On some batches this amount has been calculated incorrectly. How can
I fix this programatically? The number of batches varies within each
file and the number of 6 records varies within each batch.

While the first batch is correct, the second batch has two 6 records
and this amount is incorrect on the 8 and 9 records.

1 01 055555555 544444444 030730
5 200 BIGCO 3544444444
6 22 01111111 2 0033333333333
8 200 000001 0001111111 000000000000
9 000001 000001 00000001 0001111111
Batch Deliminiter
1 01 055555555 544444444 030730
5 200 BIGCO 3544444444
6 23 06666666 4 8888888888
6 23 05555555 4 8888888888
8 200 000001 0000000000 000000000000
9 000001 000001 00000001 0000000000
Batch Deliminiter

 

[Bob Phillips]

Michael,

Try this code

Dim f6Line As Boolean
Dim Line6Value As Long
Dim i As Long

i = 1
Do
f6Line = False
Do
If Cells(i, 1).Value > 5 Then
Select Case Cells(i, 1).Value
Case 6:
If Not f6Line Then
Line6Value = Cells(i, 3).Value
f6Line = True
Else
Cells(i, 1).EntireRow.Delete
i = i - 1
End If
Case 8: Cells(i, 4).Value = Line6Value
Case 9: Cells(i, 5).Value = Line6Value
End Select
End If
i = i + 1
Loop Until Cells(i, 1).Value = "Batch Delimiter"
i = i + 1
Loop Until IsEmpty(Cells(i, 1).Value)

 

[Bob Phillips]

Ad added thought.
Turn screenupdating and automatic calculation off before the code gets going

Application.ScreenUpdating = False
Application.calculation = xlCalculationManual

and reset at the end

Application.ScreenUpdating = True
Application.calculation = xlCalculationAutomatic

This will make it quicker, and will stop the hideous screen flashing as you
delete rows.