average on time cells, range definite with loops

Hi,

below is an example of what my worksheet looks like: (imagine it with 2400 rows)


ID Laps Car Driver S1 S2 S3 Speed Lap Pit
800 19 72 GOUESLARD Patrice 31.928 41.336 36.363 262 1:49.628 1
839 20 72 GOUESLARD Patrice 32.341 41.564 36.430 259 1:50.335 1
871 21 72 GOUESLARD Patrice 32.504 41.487 37.878 259 1:51.870 1
907 22 72 GOUESLARD Patrice 32.264 43.869 51.863 260 2:07.997 1
959 23 72 CLAIRAY Yann 48.251 41.653 35.598 262 3:05.502 2
993 24 72 CLAIRAY Yann 31.711 42.071 36.338 262 1:50.120 2
1030 25 72 CLAIRAY Yann 31.914 41.969 35.476 255 1:49.360 2

It is the document as it is given to me by the time keeping guys after the races.
It shows the lap number, the car number, the driver's name, the times in ss.ms for the 3 sectors (the track in virtually cut in 3), the maximum speed achieved on the lap, the lap time in mm:ss.ms and the number of the stint ( a stint is when the car is running in between stops)

I am trying to make automatic the analysis of the above data (more than 2400 rows for a 6h hour race, the 24 hours of Le Mans are next month...)


77 1 1-38 LIETZ Richard best
avr green

2 39-78 LIEB Marc best
avr green

3 79-117 LIETZ Richard best
avr green

4 118-153 LIEB Marc best
avr green

5 154-187 LIETZ Richard best
avr green

6 best
avr green


Above is what I have in my analysis worksheet.
The macros I already have give me the 6 first cars in the race classification, starting with the winner (above 77) then for each stint number it puts the number of the laps and the driver who was driving the car at the time.
All that works well

Now I want to write a macro that average the each of the sector times, speed and lap times for each stint by each car. And also finds the max speed and best times.

So the macro has to first find the right car, then find the right stint number. From that it needs to find the first and last lap in the stint, do the average and best and put the data in the according cell in the analysis worksheet.

I have tried lots of things but none of them really work. Now my code is all messed up and I cannot manage to fix it.

Here it is:

Sub Sector()

With Application
        .ScreenUpdating = False
        .EnableEvents = False
End With

Dim sRow As Long
Dim car As Long
Dim stint As Variant
Dim stints As Variant
Dim sum As Variant
Dim average As Variant
Dim begin As Variant
Dim debut As Long
Dim total As Variant
Dim a As Variant
average = 0
total = Array()

For car = 1 To 5
    For stint = 1 To 7
        For begin = 2 To Range("D65536").End(xlUp).Row
            If Worksheets("Race Results").Cells(begin, "C") Like Worksheets("Race Results").Range("L" & car).Value Then
                debut = begin
                For sRow = debut + 1 To Range("D65536").End(xlUp).Row
                    If Worksheets("Race Results").Cells(sRow, "J") Like "stint" And _
                        Worksheets("Race Results").Cells(sRow, "C") Like Worksheets("Race Results").Range("L" & car).Value And _
                        Worksheets("Race Results").Cells(sRow, "E").Value < 1.3 * Worksheets("Race Results").Cells(sRow, "E").Offset(-1, 0).Value Then
                        
                        a = Worksheets("Race Results").Cells(sRow, "E").Value
                        stints = stint
                    End If
                sum = sum + a
                Next sRow
                Worksheets("Stint Analysis").Range("F5").Activate
                ActiveCell.FormulaR1C1 = "=SUM('Race Results'!R[debut]C[-1]:R[sRow]C[-1])/(sRow-debut)"
                Range("F5").Select
            End If
        Next begin
    Next stint
Next car


With Application
        .ScreenUpdating = True
        .EnableEvents = True
End With

End Sub

Do you have any ideas how to fix it or do it otherwise?

Many thanks in advance!!

(I hope I formated the code properly in the thread...)