Cell Q2 trigger confusion

[osknows]

I'm attempting to forward the pick list by one when the current race has finished using

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
 
 If Target.Columns.Count = 16 Then

Application.EnableEvents = False

    Cells(2, 10).Value = "U" 'updates balance and exposure
    If Cells(2, 5).Value = "In Play" And Cells(2, 6).Value = "Suspended" And Cells(2, 17).Value <> -1 Then
        Cells(2, 17).Value = -1
       
    Else
        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
   
           
End If
Application.EnableEvents = True


However, this cyles forward 2 races and not just 1?

So I thought ok, clear out the Inplay and suspended cells to avoid re-trigger using

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
 
 If Target.Columns.Count = 16 Then

Application.EnableEvents = False

    Cells(2, 10).Value = "U" 'updates balance and exposure
    If Cells(2, 5).Value = "In Play" And Cells(2, 6).Value = "Suspended" And Cells(2, 17).Value <> -1 Then
        Cells(2, 17).Value = -1
       Cells(2, 5).Value = ""
       Cells(2, 6).Value = ""
    Else
        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
   
           
End If
Application.EnableEvents = True
End Sub


But this does the same. Can anyone see why this is?

Thanks,
Os

[greenfingers]

Afraid I'm not a good programmer so I don't understand your code, however I had a similar problem (moving forward 2 races at the end of current one) and solved it.

What I finally figured out is that once you request the next race in Q2, there is a further refresh of the data to excel before moving on to the next race, so you need to be sure not to put -1 in Q2 at this point so that it doesn't then receive a second request.

I did this by using a variable as a flag when I put -1 into Q2 and then checking the value of the flag after the refresh so that my code to put -1 in Q2 would not be re-executed.

I know I've explained this badly but hopefully it will steer you in the right direction. Cheers!

[osknows]

Thanks. I did figure it out myself and used your exact method to flag a variable whether -1 had already been triggered. It appears when BA refreshes after a trigger it clears out the cell in excel

[silver]

Is it possible for someone who has solved this issue to post the correct code? I have read osknow's description of the solution, but I don't know how to implement it.

S

[osknows]

Code: Select all

    '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
    Application.EnableEvents = False
        nextracetrigger = 1
        lastrace = Cells(1, 1).Value
        Cells(2, 17).Value = -1
     Application.EnableEvents = True
    End If
   
    If lastrace <> Cells(1, 1).Value Then
    Application.EnableEvents = False
        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)))"
    Application.EnableEvents = True
    End If


Since cell Q2 is used for both market selection and refresh rate the following line:
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)))"

just places a formula back into Q2 which sets the refresh rate to 1 sec and 0.2 thirty seconds before the off

also Cells(1, 17).Value) = "Y" is cell Q1 which i use to enable/disable forwarding of the quick pick list

[silver]

Thanks for sharing this excellent code, very helpful! The enable/disable trigger is a nice inclusion.

[mak]

Osknows hi,
I copy paste your latest code in a "Private Sub Worksheet_Change(ByVal Target As Range)" but it still forwarding 2 races..

Any ideas?

** I don't know if it has to do with the nextracetrigger, I can't understund where it "reads" 0 or 1

thanx

[osknows]

Here is a working example
http://www.mediafire.com/?sharekey=e2c0 ... b5aa27078d