q2 excel help

[andrej]

but is does not make any sense because one you move to the next market there al "0"'s in X so the -1 should disapear at that moment

[andrej]

Well, now it moved by just one market. I am having very slow connection to the internet at the moment and sometimes BA does not refresh like it should. can it be caused by this?

[osknows]

perhaps..

how about this which removes the formula when forwarding the market then once the new market is loaded replaces the formula

Code: Select all

Dim lastrace As String
Dim nextracetrigger As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

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

With ThisWorkbook.Sheets(Target.Worksheet.Name)

If .Range("e2").Value = -1 And nextracetrigger = 0 Then
.Range("e2").Value = ""
lastrace = .Range("A1").Value
.Range("Q2").Value = -1
nextracetrigger = 1

End If

If lastrace <> .Range("A1").Value Then
nextracetrigger = 0
.Range("e2").Formula = "=IF(AND(OR(X14>B3;X14="""");OR(X15>B3;X15="""");OR(X16>B3;X16="""");OR(X17>B3;X17="""");OR(X18>B3;X18="""");OR(X19>B3;X19="""");OR(X19>B3;X19="""");OR(X19>B3;X19="""");OR(X20>B3;X20="""");OR(X21>B3;X21="""");OR(X22>B3;X22="""");OR(X23>B3;X23="""");OR(X24>B3;X24="""");OR(X25>B3;X25="""");OR(X26>B3;X26="""");OR(X27>B3;X27="""");OR(X28>B3;X28="""");OR(X29>B3;X29="""");OR(X30>B3;X30=""""));-1;"""")"
End If

End With

Application.EnableEvents = True
End Sub


[andrej]

I have modified your code a bit to fit into my sheet because I have moved what e2 was into e4 and my q2 is at q11.

Dim lastrace As String
Dim nextracetrigger As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

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

With ThisWorkbook.Sheets(Target.Worksheet.Name)

If .Range("e4").Value = -1 And nextracetrigger = 0 Then
.Range("e4").Value = ""
lastrace = .Range("A10").Value
.Range("Q11").Value = -1
nextracetrigger = 1

End If

If lastrace <> .Range("A1").Value Then
nextracetrigger = 0
.Range("e4").Formula = "=IF(AND(OR(X14>B3;X14="""");OR(X15>B3;X15="""");OR(X16>B3;X16="""");OR(X17>B3;X17="""");OR(X18>B3;X18="""");OR(X19>B3;X19="""");OR(X19>B3;X19="""");OR(X19>B3;X19="""");OR(X20>B3;X20="""");OR(X21>B3;X21="""");OR(X22>B3;X22="""");OR(X23>B3;X23="""");OR(X24>B3;X24="""");OR(X25>B3;X25="""");OR(X26>B3;X26="""");OR(X27>B3;X27="""");OR(X28>B3;X28="""");OR(X29>B3;X29="""");OR(X30>B3;X30=""""));-1;"""")"
End If

End With

Application.EnableEvents = True
End Sub


the problem is that it does not do anything at all. I must have done something wrong, but I do not know what.

[mak]

If you placed the new code while BA it was logged in excel you should click on reset button in vba, or close excel & log again.

[osknows]

If lastrace <> .Range("A10").Value Then

[osknows]

If you placed the new code while BA it was logged in excel you should click on reset button in vba, or close excel & log again.

If you've stopped the code after Application.EnableEvents = False then events will be disabled. Click view/Immediate window clear whatever is in there, paste 'Application.EnableEvents = true' and hit Enter to turn on events again

[andrej]

No, it still does nothing. Are my modifications correct?

[osknows]

I can't see anything obvious. If your happy to PM a link to your spreadsheet I can take a look for you.

[andrej]

Hi again, the original E4 to Q11 is woring correctly, the problems I had must have been due to slow internet connection. Thanks for all help.

[oxerife]

Hi everyone,

The code suggested by osknows is working perfectly but as I was watching the races cycling something unplanned happened: the current race was delayed almost 15 minutes. Then the next race was already over by the time the first race ended.

Any way to prevent this? I was thinking on adding another condition such as "if time after off >= 5mins" then "cycle to next market".

Suggestions?

Thanks

[osknows]

Your suggestion of time after off > 5 mins sounds ok

Other options are:
Load each venue in different tabs
Select markets a set time before the off

I suppose it depends on your strategy and how important particular races are. For example, sometimes the time is caught up and the next race after the delay goes off on time, other times a decision is taken to delay every race by a set time which will impact all other races on the quickpick list.