N
NDBC
I am using an array to store the fastest lap time for each rider in
a team. The problem is to find the fastest lap I use the min function and
when I compare it to the reset value of the array (0/null) then 0 is always
the minimum.
I know I could just initialise the array to an enormously high time but of
course one day there could be a race with an enormously long lap and it would
fail. There must be a way around this.
This is my code. The problem is that Fast(1) and Fast (2) are always 0.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TRow As Integer
Dim TCol As Integer
Dim TeamTotal As Date
Dim Total() As Variant
Dim Fast() As Variant
Dim Slow() As Variant
Dim Count() As Variant
Dim Rider As Long
Dim MaxCol As Integer
Dim laps As Integer
If Target.Column >= 14 And Target.Row >= 5 Then
Sheets("A Grade").Unprotect
Application.ScreenUpdating = False
TRow = Target.Row
TCol = Target.Column
MaxCol = Sheets("A lap").Cells(TRow, Columns.Count).End(xlToLeft).Column
TeamTotal = 0
ReDim Count(1 To 2)
ReDim Total(1 To 2)
ReDim Fast(1 To 2)
ReDim Slow(1 To 2)
For laps = 14 To MaxCol Step 2
If Sheets("a lap").Cells(TRow, laps).Font.ColorIndex = 3 Then
Rider = 1
ElseIf Sheets("a lap").Cells(TRow, laps).Font.ColorIndex = 5 Then
Rider = 2
End If
TeamTotal = TeamTotal + Sheets("a lap").Cells(TRow, laps)
Count(Rider) = Count(Rider) + 1
Total(Rider) = Total(Rider) + Sheets("a lap").Cells(TRow, laps)
Fast(Rider) = WorksheetFunction.Min(Fast(Rider), Sheets("a
lap").Cells(TRow, laps))
Slow(Rider) = WorksheetFunction.Max(Slow(Rider), Sheets("a
lap").Cells(TRow, laps))
Next laps
'ave
For Rider = 1 To 2
If Count(Rider) > 0 Then
Sheets("a lap").Cells(TRow, Rider + 4) = Total(Rider) / Count(Rider)
End If
'Slow
Sheets("a lap").Cells(TRow, Rider + 6) = Slow(Rider)
'fast
Sheets("a lap").Cells(TRow, Rider + 8) = Fast(Rider)
Next Rider
'finish time
Sheets("a lap").Range("k" & TRow) = TeamTotal
'team average time
If Sheets("a grade").Range("j" & TRow) > 0 Then
Sheets("a lap").Range("d" & TRow) = TeamTotal / Sheets("a
grade").Range("j" & TRow)
Else
Sheets("a lap").Range("d" & TRow) = ""
End If
End If
End Sub
I know I could/should use a function to calculate some of this but the race
is on Sunday and I'm sticking to what I know for now until I have more time.
I am certainly not expecting anyone to re-write the code. I would rather have
the challenge myself anyway. I would just like to find out how to overcome
the array issue for now.
Thanks.
Was this post hel
a team. The problem is to find the fastest lap I use the min function and
when I compare it to the reset value of the array (0/null) then 0 is always
the minimum.
I know I could just initialise the array to an enormously high time but of
course one day there could be a race with an enormously long lap and it would
fail. There must be a way around this.
This is my code. The problem is that Fast(1) and Fast (2) are always 0.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TRow As Integer
Dim TCol As Integer
Dim TeamTotal As Date
Dim Total() As Variant
Dim Fast() As Variant
Dim Slow() As Variant
Dim Count() As Variant
Dim Rider As Long
Dim MaxCol As Integer
Dim laps As Integer
If Target.Column >= 14 And Target.Row >= 5 Then
Sheets("A Grade").Unprotect
Application.ScreenUpdating = False
TRow = Target.Row
TCol = Target.Column
MaxCol = Sheets("A lap").Cells(TRow, Columns.Count).End(xlToLeft).Column
TeamTotal = 0
ReDim Count(1 To 2)
ReDim Total(1 To 2)
ReDim Fast(1 To 2)
ReDim Slow(1 To 2)
For laps = 14 To MaxCol Step 2
If Sheets("a lap").Cells(TRow, laps).Font.ColorIndex = 3 Then
Rider = 1
ElseIf Sheets("a lap").Cells(TRow, laps).Font.ColorIndex = 5 Then
Rider = 2
End If
TeamTotal = TeamTotal + Sheets("a lap").Cells(TRow, laps)
Count(Rider) = Count(Rider) + 1
Total(Rider) = Total(Rider) + Sheets("a lap").Cells(TRow, laps)
Fast(Rider) = WorksheetFunction.Min(Fast(Rider), Sheets("a
lap").Cells(TRow, laps))
Slow(Rider) = WorksheetFunction.Max(Slow(Rider), Sheets("a
lap").Cells(TRow, laps))
Next laps
'ave
For Rider = 1 To 2
If Count(Rider) > 0 Then
Sheets("a lap").Cells(TRow, Rider + 4) = Total(Rider) / Count(Rider)
End If
'Slow
Sheets("a lap").Cells(TRow, Rider + 6) = Slow(Rider)
'fast
Sheets("a lap").Cells(TRow, Rider + 8) = Fast(Rider)
Next Rider
'finish time
Sheets("a lap").Range("k" & TRow) = TeamTotal
'team average time
If Sheets("a grade").Range("j" & TRow) > 0 Then
Sheets("a lap").Range("d" & TRow) = TeamTotal / Sheets("a
grade").Range("j" & TRow)
Else
Sheets("a lap").Range("d" & TRow) = ""
End If
End If
End Sub
I know I could/should use a function to calculate some of this but the race
is on Sunday and I'm sticking to what I know for now until I have more time.
I am certainly not expecting anyone to re-write the code. I would rather have
the challenge myself anyway. I would just like to find out how to overcome
the array issue for now.
Thanks.
Was this post hel