extract race lenght

[iceman]

I wrote the following macro for extrast the horse race lenght fron the cell A1. The macro copy the cell A1 in cell A55, but when the cell A55 is separeted excel ask me to sobstitute the contents of the near cells B55, B56,.........and consequently, the macro is unuseful...
Any suggestions?
Thanks anyway....

Sub Macro2()
Range("A1").Select
Selection.Copy
Range("A55").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A55"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(13, 1), Array(15, 1), _
Array(21, 1), Array(24, 1)), TrailingMinusNumbers:=True
Application.OnTime Now + TimeValue("00:00:02"), "Macro2()"
End Sub

[Ian]

You need to switch off messages before the text to columns

Application.DisplayAlerts = False

and the put it back on after

Application.DisplayAlerts = True

[osknows]

This will put the race length in A1 into A55, eg 6f, 2m1f

Code: Select all

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim racelength As String

Application.EnableEvents = False

racelength = Split(ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value, " ")(5)
ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A55").Value = racelength

Application.EnableEvents = True
End Sub


Is this what you were trying to do?

[iceman]

thanks Ian, you solve me an annoying problem...

[iceman]

This is a very nice forum.....

Thanks also to osknows!!!!

[iceman]

Osknows, I want to do that.... but if I copy your text in ThisWoorkbook, nothing happens...Probably it's my fault

[osknows]

Hello,

The above code goes into the SHEET object eg Sheet1(Sheet1) and as it's named 'Private Sub Worksheet_Change(ByVal Target As Range)' the event fires only if that sheet changes

Alternatively, you could put this in a MODULE

Code: Select all

Public Function F_racelength(racelength As String) As String

F_racelength = Split(racelength, " ")(5)

End Function


and this in a SHEET object

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A55").Value = F_racelength(ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value)

Application.EnableEvents = True
End Sub


[iceman]

thanks, it's work ok!
Now I must convert the lenght in miles, and I'll try to do somiting in play in the last seconds of the race...

[osknows]

this may help, it converts string in A55 to a number in furlungs, eg 2m1f = 17

Code: Select all

Sub convert_string_to_furlongs()
  distanceconvertfull = Range("A55").Value
       
        'miles
        If InStr(1, distanceconvertfull, "m") > 0 Then
        distanceconvertmiles = Left(distanceconvertfull, InStr(1, distanceconvertfull, "m") - 1)
        Else
        distanceconvertmiles = 0
        End If
       
        'furlong
        If InStr(1, distanceconvertfull, "f") > 0 Then
        distanceconvertfurlongs = Mid(distanceconvertfull, InStr(1, distanceconvertfull, "m") + 1, InStr(1, distanceconvertfull, "f") - InStr(1, distanceconvertfull, "m") - 1)
        Else
        distanceconvertfurlongs = 0
        End If
       
        'yards (second test is for examples such as 1m100y where no furlongs in distance string)
        If InStr(1, distanceconvertfull, "y") > 0 Then
        If InStr(1, distanceconvertfull, "f") > 0 Then
        distanceconvertyards = Mid(distanceconvertfull, InStr(1, distanceconvertfull, "f") + 1, InStr(1, distanceconvertfull, "y") - InStr(1, distanceconvertfull, "f") - 1)
        Else
        distanceconvertyards = Mid(distanceconvertfull, InStr(1, distanceconvertfull, "m") + 1, InStr(1, distanceconvertfull, "y") - InStr(1, distanceconvertfull, "m") - 1)
        End If
        Else
        distanceconvertyards = 0
        End If
       

        distance = Round((distanceconvertmiles * 8) + distanceconvertfurlongs + (distanceconvertyards / 220), 1)
       
       

End Sub


[iceman]

it's a precious code,
thanks osknows!

[ADDA]

Hi there,

I am new on this forum and have found this thread extremely useful.
I would like to be able to calculate (with some accuracy) the estimated length of a horse race. With the code above i can obtain the length of a race in furlongs, meters or miles. I would like to know if anyone has a method of converting that to a race time. i guess one could assume a horse speed and then use time = distance/velocity. however as the races get longer, the horses speed also decreases. I hope someone can help me

Regards

ADDA

[osknows]

I think Turftrax have sectional/fractional times. This article has some info but not sure how relevant or accurate it would be

[alrodopial]

Racing post site has also standard times under 'courses':
http://www.racingpost.com/horses/course_list.sd

and for each course under 'standard times'

Note that if conditions are soft/heavy actual time differs a lot from standard time

[mikey]

These are some times i found a while back:

RaceTimes

5f 57
6f 70
7f 85
1m 100
1m1f 116
1m2f 125
1m3f 144
1m4f 155
1m5f 172
1m6f 184
1m7f 195
2m 236
2m1f 251
2m2f 270
2m3f 288
2m4f 307
2m5f 321
2m6f 338
2m7f 360
3m 372
3m1f 392
3m2f 405
3m3f 420
3m4f 444
3m5f 469
3m6f 476
3m7f 504
4m 516
4m1f 540
4m2f 550
4m3f 570
4m4f 600
5f band stks 58
5f claim stks 58
5f class stks 57
5f cond stks 57
5f grp1 56
5f grp2 51
5f grp3 54
5f hcap 57
5f listed 54
5f mdn 59
5f mdn hcap 55
5f mdn stks 58
5f nov stks 56
5f nursery 58
5f sell stks 59
5f stks 59
6f band stks 73
6f claim stks 72
6f class stks 69
6f cond stks 71
6f grp1 67
6f grp2 68
6f grp3 69
6f hcap 70
6f listed 68
6f mdn 74
6f mdn hcap 68
6f mdn stks 70
6f mdn stsk 71
6f nov stks 69
6f nursery 70
6f sell hcap 70
6f sell stks 71
6f stks 73
7f band stks 86
7f claim stks 84
7f class stks 83
7f cond stks 84
7f grp1 81
7f grp2 83
7f grp3 84
7f hcap 84
7f listed 83
7f mdn 88
7f mdn hcap 79
7f mdn stks 85
7f nov stks 86
7f nursery 86
7f sell hcap 86
7f sell stks 85
7f stks 83
1m band stks 101
1m claim stks 102
1m class stks 98
1m cond stks 100
1m grp1 92
1m grp2 95
1m grp3 97
1m hcap 99
1m listed 98
1m mdn 102
1m mdn hcap 105
1m mdn stks 100
1m nov stks 101
1m nursery 99
1m sell stks 110
1m sell hcap 103
1m sell stks 100
1m stks 103
1m1f band stks 119
1m1f claim 107
1m1f claim stks 114
1m1f class stks 113
1m1f cond stks 115
1m1f grp3 107
1m1f hcap 116
1m1f listed 114
1m1f mdn 115
1m1f mdn stks 117
1m1f sell stks 120
1m1f stks 112
1m2f band stks 125
1m2f claim stks 127
1m2f class stks 126
1m2f cond stks 123
1m2f grp1 121
1m2f grp2 123
1m2f grp3 125
1m2f hcap 125
1m2f hcap stks 125
1m2f listed 126
1m2f mdn 129
1m2f mdn hcap 124
1m2f mdn stks 126
1m2f nursery 126
1m2f sell stks 125
1m2f stks 129
1m3f band stks 144
1m3f claim stks 144
1m3f class stks 150
1m3f grp3 137
1m3f hcap 142
1m3f listed 145
1m3f mdn 155
1m3f mdn hcap 141
1m3f mdn stks 143
1m3f sell stks 150
1m4f band stks 157
1m4f claim stks 156
1m4f class stks 152
1m4f cond stks 154
1m4f grp1 144
1m4f grp2 143
1m4f grp3 151
1m4f hcap 154
1m4f inhf 174
1m4f listed 150
1m4f mdn 159
1m4f mdn stks 155
1m4f nhf 169
1m4f sell hcap 155
1m4f sell stks 156
1m4f stks 155
1m5f band stks 167
1m5f claim stks 164
1m5f grp2 168
1m5f hcap 169
1m5f listed 154
1m5f mdn 157
1m5f mdn stks 163
1m5f nhf 195
1m5f plt 161
1m6f band stks 186
1m6f claim stks 183
1m6f cond stks 180
1m6f grp1 187
1m6f grp2 188
1m6f grp3 184
1m6f hcap 182
1m6f listed 177
1m6f mdn 181
1m6f mdn hcap 180
1m6f mdn stks 190
1m6f nhf 212
1m6f sell hcap 182
1m6f sell stks 187
1m6f stks 185
1m7f band stks 204
1m7f hcap 194
1m7f listed 197
2m band stks 218
2m beg chs 242
2m chs 247
2m claim hrd 229
2m claim stks 208
2m flat 230
2m grp1 chs 229
2m grp1 hrd 233
2m grp2 205
2m grp2 chs 241
2m grp2 hrd 225
2m grp3 205
2m hcap 212
2m hcap chs 244
2m hcap hrd 236
2m hrd 237
2m hunt chs 255
2m inhf 241
2m juv hrd 247
2m listed 216
2m mdn 211
2m mdn chs 247
2m mdn hrd 240
2m mdn stks 208
2m nhf 234
2m nov chs 243
2m nov hcap chs 250
2m nov hrd 238
2m sell hcap 222
2m sell hrd 238
2m sell stks 209
2m stks 224
2m1f beg chs 262
2m1f chs 256
2m1f claim hrd 244
2m1f grp1 chs 253
2m1f grp1 hrd 228
2m1f hcap 226
2m1f hcap chs 257
2m1f hcap hrd 248
2m1f hrd 227
2m1f hunt chs 252
2m1f inhf 242
2m1f mdn hcap 229
2m1f mdn hrd 250
2m1f nhf 251
2m1f nov chs 261
2m1f nov hrd 250
2m1f sell hrd 245
2m1f stks 235
2m2f beg chs 284
2m2f chs 285
2m2f class hrd 278
2m2f cond stks 243
2m2f flat 299
2m2f grp2 230
2m2f hcap 243
2m2f hcap chs 279
2m2f hcap hrd 272
2m2f hrd 266
2m2f inhf 262
2m2f listed 272
2m2f mdn chs 266
2m2f mdn hcap 237
2m2f mdn hrd 264
2m2f nhf 274
2m2f nov chs 287
2m2f nov hrd 272
2m2f sell hrd 269
2m2f stks 244
2m3f beg chs 301
2m3f chs 271
2m3f claim hrd 272
2m3f class chs 278
2m3f hcap chs 298
2m3f hcap hrd 282
2m3f hrd 289
2m3f hunt chs 294
2m3f inhf 269
2m3f mdn chs 296
2m3f mdn hrd 295
2m3f nov chs 301
2m3f nov hcap hrd 258
2m3f nov hrd 288
2m3f sell hrd 283
2m4f beg chs 313
2m4f chs 312
2m4f claim hrd 295
2m4f grp1 244
2m4f grp1 chs 310
2m4f grp1 hrd 306
2m4f grp2 chs 307
2m4f grp3 chs 343
2m4f hcap 264
2m4f hcap chs 311
2m4f hcap hrd 303
2m4f hrd 301
2m4f hunt chs 324
2m4f inhf 290
2m4f mdn hrd 305
2m4f nov chs 310
2m4f nov hcap chs 330
2m4f nov hrd 304
2m4f sell hrd 300
2m5f beg chs 324
2m5f chs 341
2m5f claim hrd 311
2m5f class hrd 329
2m5f grp2 chs 300
2m5f hcap 276
2m5f hcap chs 323
2m5f hcap hrd 313
2m5f hrd 311
2m5f hunt chs 348
2m5f listed 315
2m5f mdn chs 328
2m5f mdn hrd 319
2m5f nov chs 327
2m5f nov hrd 320
2m5f sell hrd 315
2m6f beg chs 348
2m6f chs 331
2m6f cond stks 279
2m6f hcap chs 342
2m6f hcap hrd 335
2m6f hcap hrd 342
2m6f hrd 347
2m6f hunt chs 343
2m6f mdn chs 407
2m6f mdn hrd 331
2m6f nov chs 354
2m6f nov hrd 336
2m6f sell hrd 333
2m7f beg chs 358
2m7f chs 360
2m7f class chs 373
2m7f grp2 hrd 369
2m7f hcap chs 357
2m7f hcap hrd 363
2m7f hrd 358
2m7f hunt chs 364
2m7f mdn chs 365
2m7f mdn hrd 370
2m7f nov chs 354
2m7f nov hrd 366
3m beg chs 389
3m chs 384
3m class chs 399
3m grp1 chs 373
3m grp1 hrd 345
3m grp2 hrd 350
3m hcap chs 377
3m hcap hrd 364
3m hrd 359
3m hunt chs 379
3m mdn hrd 364
3m nov chs 378
3m nov hcap hrd 345
3m nov hrd 365
3m sell hrd 362
3m1f beg chs 393
3m1f chs 400
3m1f class chs 364
3m1f grp2 chs 385
3m1f hcap chs 392
3m1f hcap hrd 390
3m1f hrd 364
3m1f hunt chs 401
3m1f mdn chs 404
3m1f mdn hrd 386
3m1f nov chs 397
3m1f nov hrd 386
3m2f beg chs 427
3m2f chs 477
3m2f class hrd 409
3m2f grp1 chs 388
3m2f hcap chs 412
3m2f hcap hrd 379
3m2f hunt chs 415
3m2f mdn chs 389
3m2f nov chs 414
3m2f nov hrd 385
3m3f hcap chs 421
3m3f hcap hrd 415
3m3f mdn chs 437
3m3f nov hrd 430
3m4f hcap chs 446
3m4f hunt chs 445
3m4f nov chs 422
3m5f hcap chs 469
3m6f hcap chs 476
3m7f chs 515
3m7f hcap chs 501
4m hcap chs 516
4m1f hcap chs 555
4m1f nov chs 510



mike..

[mikey]

Sorry that was standard race times in seconds not race lengh.