Selecting next race

[olisav]

Hi,

I might be missing something very simple, but I want to trade a market when it goes in play. Once the race is over I want it to automatically go onto the next race in the quick pick list where i have all win uk races.

If I click on wait until the actual off, it goes on to the next race before the race has finished.

I have looked about in the forum and thought that the Q2 trigger -1 might work, however I am not using macros and my formula in Q2 was deleted.

I am sure this must be a simple thing, but have spent a while searching the forums for something.

Does anyone have any idea on how i can do this.

Many thanks.

Oliver

[osknows]

This will work for you placed in the sheet object of your VBA compiler

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
Application.EnableEvents = False

       
    'forward quick pick race on after race ended
    If Cells(2, 5).Value = "In Play" And Cells(2, 6).Value = "Suspended" And UCase(Cells(1, 17).Value) = "Y" And nextracetrigger = 0 Then
        nextracetrigger = 1
        lastrace = Cells(1, 1).Value
        Cells(2, 17).Value = -1
    End If
   
    If lastrace <> Cells(1, 1).Value Then
        nextracetrigger = 0
        Cells(2, 17).FormulaR1C1 = "=IF(OR(RC[-11]=""Suspended"",RC[-11]=""Closed""),1,IF(ISERROR(IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,1)),0.2,IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,1)))"
    End If
   
Application.EnableEvents = True
End If

End Sub


[olisav]

Thanks for the reply.

I was hoping not to use VBA. Can anyone think of a way to do this not using VBA.

[GaryRussell]

Sorry, there isn't currently an option to do this without using VBA.

[olisav]

Thanks Gary and Osknows, I will set this up in VBA.

[olisav]

Hi,

I have adapted the code a bit so it is in line with my spreadsheet. I do not have anything in Cell 1.17, must be using an older version.

Anyway, the problem I have now is that it goes on two races, so you know how to stop this?

Cheers,

Oli


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
Application.EnableEvents = False

'forward quick pick race on after race ended
If Cells(2, 5).Value = "In Play" And Cells(2, 6).Value = "Suspended" And Cells(2, 17).Value = 0.2 Then
nextracetrigger = 1
lastrace = Cells(1, 1).Value
Cells(2, 17).Value = -1

End

End If

If lastrace <> Cells(1, 1).Value Then
nextracetrigger = 0
Cells(2, 17).FormulaR1C1 = "=IF(OR(RC[-11]=""Suspended"",RC[-11]=""Closed""),0.2,IF(ISERROR(IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,0.2)),0.2,IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,0.2)))"
End If

Application.EnableEvents = True
End If

[olisav]

Sorry, had an extra end in, this is the corrected code.



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
Application.EnableEvents = False

'forward quick pick race on after race ended
If Cells(2, 5).Value = "In Play" And Cells(2, 6).Value = "Suspended" And Cells(2, 17).Value = 0.2 Then
nextracetrigger = 1
lastrace = Cells(1, 1).Value
Cells(2, 17).Value = -1


End If

If lastrace <> Cells(1, 1).Value Then
nextracetrigger = 0
Cells(2, 17).FormulaR1C1 = "=IF(OR(RC[-11]=""Suspended"",RC[-11]=""Closed""),0.2,IF(ISERROR(IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,0.2)),0.2,IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,0.2)))"
End If

Application.EnableEvents = True
End If