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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200902


"NDBC" wrote:

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