Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > clearing array variables and using an array to determine min value
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default clearing array variables and using an array to determine min value
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,058
Default clearing array variables and using an array to determine min value
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default clearing array variables and using an array to determine min value
try omtting if the lap time is zero -- I've amended th code below to give you
teh idea...

"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


laptime = Sheets("a lap").Cells(TRow, laps)
IF laptime0 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

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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default clearing array variables and using an array to determine min v
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" wrote:

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

"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


laptime = Sheets("a lap").Cells(TRow, laps)
IF laptime0 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

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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default clearing array variables and using an array to determine min v
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default clearing array variables and using an array to determine min v
you calc the avge in the code below that

"NDBC" wrote:

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" wrote:

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

"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


laptime = Sheets("a lap").Cells(TRow, laps)
IF laptime0 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

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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default clearing array variables and using an array to determine min value
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" 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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default clearing array variables and using an array to determine min v
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).


"Patrick Molloy" wrote:

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" 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
clearing an array NDBC Excel Programming 8 September 5th 09 05:43 AM
clearing an array mark Excel Programming 5 June 25th 07 11:23 PM
Clearing Array Contents Ken Hudson Excel Programming 1 May 5th 07 07:35 PM
Array for clearing checkboxes Richard Excel Programming 6 August 12th 05 08:37 AM
clearing array contents mark Excel Programming 15 June 8th 05 03:51 PM


All times are GMT +1. The time now is 01:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement