Race Outcome detection with automated quick pick list

[Fixador]

hi Mitch, now got through to 'suspended' with the following code, based on yours.............

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False

If Cells(11, 5) <> "In Play" Then
    Cells(1, 24) = "NO"
    Cells(1, 25) = ""
    'Debug.Print "NO"
    Application.EnableEvents = True
    Exit Sub
End If

If (Cells(11, 5) = "In Play") And (Cells(1, 24) = "NO") Then
    Cells(1, 24) = "WAIT"
    Debug.Print "wait"
    Cells(1, 25) = Cells(11, 3)
End If

If (Cells(11, 5) = "In Play") And (Cells(11, 3) - Cells(1, 25) >= TimeSerial(0, 0, 30)) And Cells(1, 24) <> "OK" _
            And Cells(1, 24) = "WAIT" Then
    Cells(1, 24) = "OK"
    Debug.Print "ok" 'ran once - good !
    Application.EnableEvents = True
    Exit Sub
End If
'########################## now 20 secs into in play

If (Cells(11, 6) <> "Suspended") And (Cells(1, 24) = "OK") Then
    Cells(2, 24) = "NO- sus yet"
    Cells(2, 25) = ""
    Debug.Print "NO- sus yet"
    Application.EnableEvents = True
    Exit Sub
End If

If (Cells(11, 6) = "Suspended") And (Cells(2, 24) = "NO- sus yet") Then
    Cells(2, 24) = "WAIT for sus"
    Debug.Print "wait"
    Cells(2, 25) = Cells(11, 3)
End If

If (Cells(11, 6) = "Suspended") And (Cells(11, 3) - Cells(2, 25) >= TimeSerial(0, 0, 30)) And Cells(2, 24) <> "OK-sus" _
            And Cells(2, 24) = "WAIT for sus" Then
    Cells(2, 24) = "OK-sus"
    Debug.Print "OK-sus"
    Application.EnableEvents = True
    Exit Sub
End If
Application.EnableEvents = True

End Sub


A bit rough - but works,
I want to add a check ( as a confirmation of suspended ) - basically sum(c13:m50) - is there a way to do this purely in vba ?

I reckon i am getting close to wanting to examine Col O - Mitch is there any vba code for determining the winner ? preferably I would like to grap the runners name - and copy it elsewhere

Paul

[Mitch]

You can use any formula that's available from the worksheet by using Worksheetfunction, so "=SUM(C13:M50)" as a formula on your worksheet would become "variableName = WorksheetFunction.Sum(Range("C13:M50"))".

The easiest way off the top of my head to determine the winner (if you're going to use the lowest last matched price) is again by using Worksheet functions, like this...

lowOdds = WorksheetFunction.Min(Range("O5:O50"))
lowRow = WorksheetFunction.Match(lowOdds,Range("O5:O50"), 0)
lowName = Cells(lowRow, 1)

There might be a better way but I think that should work, though I've not tested it.

[Fixador]

Thanks Mitch,

I'll give that a whirl later. I did VB many years ago , but never VBA - so all the long instruction names are quite daunting ! Ok, you could say they are in VB as well - but i started with VB - before it had a 'V' in it ! Think Qbasic was my starter - lovely short varaible names - ha ha ! 30 years ago.

The prior stuff seems to be working ok, i have added a little code to get it to roll to the next race when A10 changes

cheers

Paul

[Fixador]

Hello Mitch , yr code:-

Code: Select all

lowOdds = WorksheetFunction.Min(Range("O5:O50"))
lowRow = WorksheetFunction.Match(lowOdds,Range("O5:O50"), 0)
lowName = Cells(lowRow, 1)


almost right - just one change

Code: Select all

lowName = Cells(4 + lowRow, 1)


Also LowOdds has to be double, and i could ownly get to work if LowName is variant

Thanks very much , now got LowName to debug.print - and sh1t , one of my Lays won !

Paul

[Mitch]

Hello Mitch , yr code:-

almost right - just one change

Code: Select all

lowName = Cells(4 + lowRow, 1)


Also LowOdds has to be double, and i could ownly get to work if LowName is variant

Thanks very much , now got LowName to debug.print - and excrement , one of my Lays won !

Paul

Well spotted - sorry about that.