Ensuring Workbook is 'seeing' same Win Mkt and Place Mkt

[Fixador]

Hi All,

One workbook - assume 2 sheets ( sht1 & sht2 ) :-

One BA tab loads quickpicklist for Win mkt via sht1.cells(2,17) = -3.1

A second BA tab loads quickpicklist for Place mkt via sht2.cells(2,17) = -3.2

To load first race in each sheet using .cells(2,17) = -5

then rolling to the next race via .cells(2,17) = -1

But, given the occassional qlitch - say internet connection briefly lost - then I need to ensure that both sheets are 'seeing' the same race..... or, sht2 is 'seeing' the same race as sht1

How check ?

[Ian]

You could do a vlookup on the favourite in sheet1 and ensure that it's found in sheet2.

[alrodopial]

I'm sure you can adjust the below:

=IF(LEFT(A1,FIND("-",A1)+6)= LEFT(Sheet2!A1,FIND("-",Sheet2!A1)+6),"YES","NO")

[Fixador]

cheers guys,

I can see this is going to want some careful planning - as thinking it through a bit more - as, if there isnt a match , then I dont know whether I need to get sht2 to step forward or backward !

Nothing for it, other than anylsis the 'time' of the race in cell A1 - drats, i was hoping to stay away from that !

[Fixador]

......more mutterings from an ex-spurt...

firing special triggers into .cells(2,17) to shift races only works if NOT using BA's own "Market" - "Auto Select Market" -"At a specific time before the OFF"......

Doesn't it ? I dont know . I do know : I can't manually select a different race if i have "Auto Select" enabled.

So, the lazy way out is to use "Auto Select" for both the win and place mkt.

job done !

But is it ?

Yesterday - using "AutoSelect" - I had all of the Southwell races in my quickpicklist along with Lingfield - but once Southwell stopped because of the fog - BA stopped on the 1330 race - and "Auto Select" didnt roll forward to the Lingfield races.

Did anyone else experience this yesterday ?

Are there any 'special' cells in Excel that indicate that a race is cancelled - as this being winter - this is going to happen again !

On from this specific example - can I get Excel to invoke a raceroll when "Auto Select" is enabled ?

[GaryRussell]

......more mutterings from an ex-spurt...

firing special triggers into .cells(2,17) to shift races only works if NOT using BA's own "Market" - "Auto Select Market" -"At a specific time before the OFF"......

Doesn't it ? I dont know . I do know : I can't manually select a different race if i have "Auto Select" enabled.

So, the lazy way out is to use "Auto Select" for both the win and place mkt.

job done !

But is it ?

Yesterday - using "AutoSelect" - I had all of the Southwell races in my quickpicklist along with Lingfield - but once Southwell stopped because of the fog - BA stopped on the 1330 race - and "Auto Select" didnt roll forward to the Lingfield races.

Did anyone else experience this yesterday ?

Are there any 'special' cells in Excel that indicate that a race is cancelled - as this being winter - this is going to happen again !

On from this specific example - can I get Excel to invoke a raceroll when "Auto Select" is enabled ?

There is no special cell, F2 should have contained "CLOSED" though. It's supposed to still be able to move to the next race even if the previous is cancelled. I'll investigate.

[GaryRussell]

To help with my testing can you tell me which auto select option you used? I assume from your previous post it was "At specific time before the off"

[Captain Sensible]

There was a discussion of matching up the win and place markets on this thread

http://gruss-software.co.uk/forum/viewt ... c&start=30

osknows posted up a working sheet that ensured they matched , I think the lnks expired so put a copy here

http://www.mediafire.com/file/38oibdoa0 ... to_win.xls

[Fixador]

Hi Gary,

using BA ver 1.0.0.66x37 on 2 pc's - both 'stopped' :-

Yes "Auto Select" was set to "At specific time before the off"

Thank you for heads up on "F2 should have contained "CLOSED" "

Gary - I can incorporate... If F2="CLOSED" to fire a Q2 trigger ="-1" into my vba - but will it will overide "At specific time before the off" ???


Cpt Sensible - cheers - ah ha ! Osknows has been busy again has he ! I do terrible things when i extend his coding ... shameful things !

[GaryRussell]

Gary - I can incorporate... If F2="CLOSED" to fire a Q2 trigger ="-1" into my vba - but will it will overide "At specific time before the off" ???

"At specific time before off" will still be active. I am now not sure if this will solve your problem though because after testing (I simulated an abandoned meeting) I found it should have changed markets so it must have got stuck for a reason I am not yet aware of which may also stop the -1 trigger working. The only thing I can do is test it daily until they abandon a meeting.

[GaryRussell]

Did you initially manually select the first market in the quick pick list or did you leave it without a market selected with the intention that auto select would select the first market?

[Fixador]

Hi Gary,

For that particular workbook :Quickpicklist population and select first race are via ONTIME call to subs, subsequent races are loaded via Auto Select.

Checking back : Another workbook that also 'stopped' yesteday : also uses :Quickpicklist population and select first race via ONTIME call to subs - but, subsequent race is loaded also via an ONTIME for the Q2 trigger call.

In this second workbook - one of the conditions within the vba is sht.Cells(11, 6) = "Closed" ( where A10 is 'first cell' for BA ) to roll to next race.

"Closed" or "CLOSED" .......... ?

My workbooks loggers only records when quickpicklist loaded, and when first race loaded - not Q2 triggers for subsequent races .

[GaryRussell]

"Closed" or "CLOSED" .......... ?

"Closed" is correct. Sorry, no idea why I stated in upper case.

[Fixador]

thank you Gary - timely.... just reviewing both of workbooks now

[Fixador]

(1) A place mkt missing from my list - the 1250
(2) A curious Market id for todays 1655 ! N2 = 0

this is the code ( I have switched from OSknows arrays - to a tabulation )
( note : BA first cell is A10 not A1 - so it is N12 that returned =0 )

Code: Select all

Public Sub Mkt_Intialisation()
Dim lastrace As String, nextracetrigger As Integer, marketcount As Integer, sht7 As Worksheet, counter As Integer
Set sht7 = ThisWorkbook.Worksheets("Plc Mkt Betting")
'If Target.Columns.Count <> 16 Then Exit Sub' this is in worksheet_change which is calling proc
ThisWorkbook.Worksheets.Application.EnableEvents = False: ThisWorkbook.Worksheets.Application.Calculation = xlCalculationManual

If sht7.Cells(1, 1) = "First Race Loaded" Then
    'If (sht7.Cells(11, 6) = "Closed") Then
        'Application.OnTime Now + TimeValue("00:00:05"), "my_RollDelay2"
    'Else:
        'load array once only - forget arrays - switch to a tabulation on sht7 col 30,31
        If sht7.Cells(1, 16) = 0 Or sht7.Cells(1, 16) = "" Then
            nextracetrigger = sht7.Cells(7, 1): counter = sht7.Cells(7, 2)
            If nextracetrigger = 1 Then
                If InStr(1, sht7.Cells(10, 1), sht7.Cells(counter, 30), vbTextCompare) > 0 Then 'need to raceroll
                    If sht7.Range("J12").Value <> "L" Then
                        Application.OnTime Now + TimeValue("00:00:05"), "my_Roll_Delay4"
                        Exit Sub ' ?????
                    End If
                Else:
                    If InStr(1, sht7.Cells(10, 1), sht7.Cells(counter, 30), vbTextCompare) <= 0 Then 'race roll has occurred
                        counter = 1 + sht7.Cells(7, 2)
                        sht7.Cells(7, 2) = counter
                        sht7.Cells(counter, 30) = Replace(sht7.Range("A10"), " To Be Placed", "")
                        sht7.Cells(counter, 31) = sht7.Range("N12")
                    End If
                End If
                If sht7.Range("J12").Value = "L" Then
                    nextracetrigger = 2 ' no further response !
                    sht7.Cells(7, 1) = nextracetrigger
                    sht7.Cells(1, 16) = "DONE"
                    sht7.Cells(7, 2) = counter
                End If
            End If
            If nextracetrigger = 0 Then
                nextracetrigger = 1: counter = 1
                sht7.Cells(7, 1) = nextracetrigger: sht7.Cells(7, 2) = counter
                sht7.Cells(counter, 30) = Replace(sht7.Range("A10"), " To Be Placed", "")
                sht7.Cells(counter, 31) = sht7.Range("N12")
            End If
        End If
    'End If
End If
ThisWorkbook.Worksheets.Application.Calculation = xlCalculationAutomatic: ThisWorkbook.Worksheets.Application.EnableEvents = True
End Sub


And the list of races and market titles and id's in sht 7 cols 30 & 31 :-

[/img][/list]