clearing array variables and using an array to determine min value

[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

 

[Gary''s Student]

You can get MIN to ignore zeros by using blanks instead:

Sub MinTest()
Dim fast(1 To 4) As Variant
fast(1) = ""
fast(2) = 2
fast(3) = 3
fast(4) = 4
MsgBox (Application.WorksheetFunction.Min(fast))
End Sub

 

[Patrick Molloy]

try omtting if the lap time is zero -- I've amended th code below to give you
teh idea...

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

laptime = Sheets("a lap").Cells(TRow, laps)
IF laptime>0 then

TeamTotal = TeamTotal + laptime
Count(Rider) = Count(Rider) + 1
Total(Rider) = Total(Rider) + laptime
Fast(Rider) = WorksheetFunction.Min(Fast(Rider),laptime)
Slow(Rider) = WorksheetFunction.Max(Slow(Rider), laptime)

End If

 

[NDBC]

Thanks patrick but I don't think it's the laptime that is causing the min to
be zero, I think the minimum is always the value the array gets when it is
redim. I think redim makes fast(1)=0 etc

 

[NDBC]

I've got 2003 and it doesn't like fast(1)="" being used in the min function

 

[Patrick Molloy]

you calc the avge in the code below that

Thanks patrick but I don't think it's the laptime that is causing the min to
be zero, I think the minimum is always the value the array gets when it is
redim. I think redim makes fast(1)=0 etc

 

[Patrick Molloy]

I don't see the sheet from your description, but you can use the CHANGE event
to post the date/time into another cell

for example, if any cell in columns F,G of H change, tyhe date/time goes
into C2


Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 6 To 8
Range("C2") = Now
Case Else
End Select
End Sub

if you wanted to check if a cell could have been altered in one of two or
more areas, then you could use this example:
I have blocks C5:E11, G13:18
record in C2 when a value is entered

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim found As Range
Set found = Intersect(Target, Union(Range("C5:E11"), Range("G13:G18")))
If Not found Is Nothing Then
Range("C2") = Now
End If
End Sub

 

[NDBC]

It finally came to me. To ensure it all works I need to initially set the
fast(1) and fast(2) to the maximum lap time to that point in the race. Then I
apply the min function as before for each rider to get the two fastest laps.
Then if one of the two riders hasn't finished a lap (count(i) = 0) then set
fast(i) ="" at the end. This way there are no risks with a wrong number being
saved in fast(i).