Hi I am reasonably inexperienced with complex excel issues.
I am a member of a motorsport club who has an upcoming event on saturday (yes only in a few days time!!) and I have a spreadsheet that isn't doing what I need it to, to record our competitors times!
The format of the event it that there are 3 tracks and each of the 30 drivers have 3 goes at each track. Only their fast time from each track gets added together to come up with their total score.
My current spreadsheet did work perfectly if I only needed to account for minutes and seconds but I really need it to include the hundredth's of a second (mm:ss.00)
I had a go at VBA (for the first time - didn't know this existed) using a code I found while searching the internet and it was working (but for some unknown reason it no longer is?????)
This was the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Intrsct As Range, Cell As Range, s As String
Dim mins As Long, secs As Long, t As Date
Set Intrsct = Intersect(Range("I6:J35,L6:M35,O6:P35,S6:T35,V6:W35,Y6:Z35,AC6:AD35,AF6:AG35,AI6:AJ35,AM6:AN35,AP6:AQ35,AS6:AT35"), Target)
If Intrsct Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cell In Intrsct
s = Cell.Text
If s = "" Then
Cell.NumberFormat = "@"
ElseIf Len(s) > 4 Then
Cell.Clear
Cell.NumberFormat = "@"
Else
s = Right("0000" & s, 4)
If s Like "####" Then
mins = CLng(Left(s, 2))
secs = CLng(Right(s, 2))
t = TimeSerial(0, mins, secs)
Cell.NumberFormat = "[mm]:ss"
Cell.Value = t
Else
Cell.Clear
Cell.NumberFormat = "@"
End If
End If
Next Cell
Application.EnableEvents = True
End Sub
But like I said it didn't work for hundredth's of a second.
Question 1: can this be modified to include hundredth's of a second, mm:ss.00 if so please show me how.
Question 2: is there a completely better way?
Question 3: why did it stop working today when every other time I have opened the spreadsheet it was fine - is there a setting that I need to change?
I am a member of a motorsport club who has an upcoming event on saturday (yes only in a few days time!!) and I have a spreadsheet that isn't doing what I need it to, to record our competitors times!
The format of the event it that there are 3 tracks and each of the 30 drivers have 3 goes at each track. Only their fast time from each track gets added together to come up with their total score.
My current spreadsheet did work perfectly if I only needed to account for minutes and seconds but I really need it to include the hundredth's of a second (mm:ss.00)
I had a go at VBA (for the first time - didn't know this existed) using a code I found while searching the internet and it was working (but for some unknown reason it no longer is?????)
This was the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Intrsct As Range, Cell As Range, s As String
Dim mins As Long, secs As Long, t As Date
Set Intrsct = Intersect(Range("I6:J35,L6:M35,O6:P35,S6:T35,V6:W35,Y6:Z35,AC6:AD35,AF6:AG35,AI6:AJ35,AM6:AN35,AP6:AQ35,AS6:AT35"), Target)
If Intrsct Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cell In Intrsct
s = Cell.Text
If s = "" Then
Cell.NumberFormat = "@"
ElseIf Len(s) > 4 Then
Cell.Clear
Cell.NumberFormat = "@"
Else
s = Right("0000" & s, 4)
If s Like "####" Then
mins = CLng(Left(s, 2))
secs = CLng(Right(s, 2))
t = TimeSerial(0, mins, secs)
Cell.NumberFormat = "[mm]:ss"
Cell.Value = t
Else
Cell.Clear
Cell.NumberFormat = "@"
End If
End If
Next Cell
Application.EnableEvents = True
End Sub
But like I said it didn't work for hundredth's of a second.
Question 1: can this be modified to include hundredth's of a second, mm:ss.00 if so please show me how.
Question 2: is there a completely better way?
Question 3: why did it stop working today when every other time I have opened the spreadsheet it was fine - is there a setting that I need to change?