Q2 HELP

[BERTRAND]

Hi
I want to achieve the following function in Q2,

Code: Select all

IF(AND(E2="In Play",F2="Suspended"),-1,"")


It seems that I need VBA to do this.
Can someone point me in the right direction.
Many thanks
Bert

[GaryRussell]

There is an example here http://gruss-software.co.uk/forum/viewtopic.php?t=5163

[BERTRAND]

Hi Gary,
Yes I looked at that code earlier, but it's timing based. I only need to refer to the contents of E2 and F2. -so that the market changes immediately the race finishes. (Inplay markets only)
Bert

[GaryRussell]

Put the following code in sheet1. Please note that because it can be in play and suspended briefly at the start of the race it only triggers when it has been in play for at least 20 seconds.

Code: Select all

Option Explicit

Dim currentMarket As String
Dim inPlay As Boolean
Dim inPlayTime As Date
Dim marketChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        If [A1] <> currentMarket Then
            currentMarket = [A1]
            inPlay = False
            marketChanged = False
        End If
        If [E2] = "In Play" Then
            If Not inPlay Then
                inPlay = True
                inPlayTime = Now
            End If
            If [F2] = "Suspended" And Not marketChanged And DateDiff("s", inPlayTime, Now) > 20 Then
                marketChanged = True
                Application.EnableEvents = False
                [Q2] = "-1"
                Application.EnableEvents = True
            End If
        Else
            inPlay = False
        End If
    End If
End Sub

[BERTRAND]

Thanks for your time Gary, I'm sure the code will be useful for many members.

The reasons I desired this function were twofold, firstly if a meeting is way behind schedule (meetings in separate tabs, and triggering inplay) then I don't miss a race.

Secondly--It will massively reduce my data requests. My default refresh time of 0.2s kicks in 30sec before a race, otherwise it is 1s. But all the time the inplay flag is true and the race has finished it is still requesting at 8-10rps.

Cheers
Bert

[SenseiCRO]

Hello,

I have some different code for switching races. I want to switch to the next race in the Quick Pick list when the current race goes to "Suspended" status when it is in the "In Play" mode.

Here is my code:

If Me.Cells(2, 5).Value = "In Play" And Me.Cells(2, 6).Value = "Suspended" Then

[Q2] = -1
End If


The problem is that it switches to the next race, but when it shows in the grid, it switches to the second race. So, it doesn't advance 1 race, but 2 races.

Where's my code wrong?

Thanks for your help.

Cheers everyone,
Dean

[osknows]

Hi Dean,

Use Gary's code above. If you remove the bolded part from the line below it will perform exactly your requirements or leave it there for added protection

If [F2] = "Suspended" And Not marketChanged And DateDiff("s", inPlayTime, Now) > 20 Then

[BERTRAND]

When I paste the code underneath the timer code I use in sheet 1, the workbook will not link to BA. Help!