Circling through horse racing markets

[danjuma]

Having a look at it again, was working ok (races syncing), then got to the third race in the place market, and place market just stuck on that race while win market continues jumping through the races. Just erratic, the way it's working.

[Captain Sensible]

Let's say I have race 1 to 10 in the quick pick list for the win market, and the place equivalent 1 to 10 in the place market. I link race 1 to sheet Win first, then while linking race 1 in the place market to sheet Place, sheet Win would have jumped to next race (same thing happens whether I link the place market first before the win market).

X1 will always be 0 when you start as they don't match up that means you code

If Range("X1").Value = 0 And Range("J3").Value <> "L" Then
Range("Q2").Value = -1
ElseIf Range("X1").Value = 0 And Range("J3").Value = "L" Then
Range("Q2").Value = -5
End If

will fire off


Sheet win continues jumping through the races until it comes back to race 1 and then in sync with race 1 in the place market. It then stays to check conditions, and then jump to next race (race 2). Sheet Place also then jumps to race 2, but then remains on race 2, while sheet Win jumps through all the races again and come back to race 2 to sync with sheet Place. And so it goes on.

Again the win part of the sheet is always moving first so once it's moved off the match it won't be in sync with a market and needs to cycle thru them all to get back in sync with the place market. You need to have to win market as the main market and be forcing the place market to 'catch up' at the moment you'll be in a stituation where the win market moves first and because it's not automatically matching it cycles thru all markets until it matches and the rest of the code executes.

You need to be allowing the place market time to catch up otherwsie it'll just continue with the win market always looping thru all markets before it eventually matches up again. Whats your code for x1 and I'll see if I knock one up

[Captain Sensible]

You basically just need to allow time for the place market to catch up as BA wont move both markets at excatly the same time due to the fact each market will refresh independantly.

[danjuma]

You need to be allowing the place market time to catch up otherwsie it'll just continue with the win market always looping thru all markets before it eventually matches up again. Whats your code for x1 and I'll see if I knock one up

Thanks for your assistance CS.

I extract the following info from the win and place markets into cells J20 and J21 (in a sheet named 'Control') respectively using the formula =LEFT(Win!A1,FIND(":",A1)+2). So cells J20 and J21 will display info like the following FflosL 20th Jul - 14:30. Though for some reason, sometimes it will display as FflosL 20th Jul - 14:3 (without the last zero), not sure why?
X1 basically checks the data in J20 and J21 and if they are the same returns 1. If(Control!J20=Control!J21,1,0)

Many thanks

[Captain Sensible]

Just had a look at it and things that should work don't for some reason must be down to way BA handles separate tabs or more than likely my poor coding

A lot appears to be down to the delay in excel reporting the info to the screen so you need to effectively add one to the refresh so the code can realise there's a match in markets before firing into the Q2 cell.

I came up with this code to force match the markets to match for some reason it didn't work well on the win sheet but fine on the place sheet again more than likley down to the fact the sheets are refreshing separately so one sheet may update before the other causing it to give lagged data to the worksheet change call. Probably best to let the place sheet play catch up and maybe add some control cell to your sheet to stop and start it, just something simple like putting "y" in cell T1

ie If Range("T1").Value <> "y" Then Exit Sub


Here's the code I came up with, like I say for some reason it didn't like being in win but worked fine in place just matching itself up with the win sheet if the markets didn't match


Dim delay As Integer
Dim win As String
Dim place As String
Dim match As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

win = Left(Worksheets("win").Cells(1, 1), 21)
place = Left(Worksheets("Place").Range("A1").Value, 21)

delay = delay + 1

If place = win Then
match = "match"
delay = 0
Else
match = "no match"

End If

If Worksheets("Place").Range("J3").Value <> "L" And match <> "match" And delay = 2 Then
Worksheets("Place").Range("Q2").Value = -1
delay = 0
ElseIf Worksheets("Place").Range("J3").Value = "L" And match <> "match" And delay = 2 Then
Worksheets("Place").Range("Q2").Value = -5
delay = 0
End If


End Sub

[danjuma]

Thanks CS,

How would I incorporate this with my code? I tried the code below, but was doing the same thing as before (ie. place market jumping and win market staying still).

Dim win As String
Dim place As String
Dim match As String
Dim counter As Integer


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

win = Left(Worksheets("win").Cells(1, 1), 21)
place = Left(Worksheets("Place").Range("A1").Value, 21)

delay = delay + 1

If place = win Then
match = "match"
delay = 0
Else
match = "no match"

End If

If Worksheets("Place").Range("J3").Value <> "L" And match <> "match" And delay = 2 Then
Worksheets("Place").Range("Q2").Value = -1
delay = 0
ElseIf Worksheets("Place").Range("J3").Value = "L" And match <> "match" And delay = 2 Then
Worksheets("Place").Range("Q2").Value = -5
delay = 0
End If

If Range("X2").Value = 0 Then
match = "no match"
Else
counter = counter + 1
End If
If counter > 10 Then
match = "no match"
End If
Application.EnableEvents = True
End Sub

[Captain Sensible]

I just had it as a separate worksheet_change sub in the place sheet rather than under the win sheet . It'll then just play catchup if the markets are out of line.

All your other coding to move on the win sheet then justs need to be in your sub under the win sheet, in the example you posted below there's nothing there to move on the win sheet

[Captain Sensible]

dan I've stuck the file I was testing up here http://www.mediafire.com/?easdbjshzp27tpd as it'll probably be easier to understand

I tried to set it to the details you'd given but obviously I haven't your sheet so you'll still need to amend certain things to get it to do what you need. I just set it so if the win market and place didn't match it will force the place market to catch up. Once they're matched because X1 =1 it'll then refresh 10 times then move the win market forward, because the markets will no longer match the place market will then move forward one to match and so on.

I put the market moving code into separate worksheet codes because the markets won't ever refresh at the exactly the same time

[danjuma]

Thanks so much CS.

Just popping out. Will try out when I come back and see how it goes. Cheers

[danjuma]

Hi CS,

This proving more difficult than I thought.

I have just downloaded your sheet and ran it exactly as it is, didn't change anything, just to see how it works before I start doing any amendments. Linked the sheets (win and place) up with the same race (win race and the place equivalent). The sheets jumped through the races unsynchronised for a while, then eventually synchronised. Then got to the third race on the list, and the win market got stuck, while the place market kept going through all the races. One thing I noticed, as I see you have outputted the counter to cell U2, is that the counter seems to stall. So it might go 1, 2 then stall for a while, then jump to 5, or just remains stalled and not change to show its counting.

Cheers

[osknows]

One suggestion which may help.. if you load place markets in the quickpick list, use vba to cycle through one time only and load the 'event name' and 'market id' in a 2 dimensional array

eg for 10 markets

Dim placemarket(1 to 10, 1 to 2)

when loaded would look something like
Event A, 12345
Event B, 12346
Event C, 12347
.
.
etc

Then in the win market find a match for the event name in the placemarket() array and fetch the market by placing GO:market id for matching market in the array

Eg if Event A is matched place 'GO:12345' in cell Q2 of place sheet

Does that make sense??

[danjuma]

Does that make sense??

Probably does osknow
Only problem is a VBA novice here
How do I code/implement your suggestion? Arrays etc are French to me

[Captain Sensible]

One suggestion which may help.. if you load place markets in the quickpick list, use vba to cycle through one time only and load the 'event name' and 'market id' in a 2 dimensional array

eg for 10 markets

Dim placemarket(1 to 10, 1 to 2)

when loaded would look something like
Event A, 12345
Event B, 12346
Event C, 12347
.
.
etc

Then in the win market find a match for the event name in the placemarket() array and fetch the market by placing GO:market id for matching market in the array

Eg if Event A is matched place 'GO:12345' in cell Q2 of place sheet

Does that make sense??

That's interesting stuff osknows, I wasn't aware we could now navigate using the marketid even though I remember requesting the marketid filed be added to the worksheet

[osknows]

See if this does what you need? http://www.mediafire.com/?2n1ejb1yu6y46t2

Two seperate tabs, win & place containing same list of races. The code cycles though the place market and loads the array. When the win market is navigated the place market follows...hopefully

[danjuma]

Hi osknows,

Ok, I have added my code to your code for the win sheet as shown below. Your code for the place sheet I have not touched.

Code: Select all

Dim lastrace As String
Dim nextracetrigger As Integer
Dim counter As Integer


Private Sub worksheet_Change(ByVal Target As Range)


'only run if arrayloaded and BA 16 column update
If Target.Columns.Count <> 16 Or Not arrayloaded Then Exit Sub
Application.EnableEvents = False





'change place market to match win market
If InStr(1, ThisWorkbook.Sheets("Win").Range("A1").Value, Replace(ThisWorkbook.Sheets("Place").Range("A1").Value, " To Be Placed", ""), vbTextCompare) = 0 Then
arraycount = 0
Do Until InStr(1, ThisWorkbook.Sheets("Win").Range("A1").Value, placemarketarray(arraycount, 0), vbTextCompare) = 1
arraycount = arraycount + 1
Loop
ThisWorkbook.Sheets("Place").Range("Q2").Value = "GO:" & placemarketarray(arraycount, 1)
nextracetrigger = 1
End If


'normal code in here

If Range("x1").Value = 1 And Range("X2").Value = 1 Then
counter = counter + 1
End If
If counter > 10 And Range("J3").Value = "L" Then
counter = 0
Range("Q2").Value = -5
ElseIf counter > 10 Then
counter = 0
Range("Q2").Value = -1
End If


If Range("X1").Value = 1 And Range("X2").Value = 0 Then
If Range("J3").Value <> "L" Then Range("Q2").Value = -1
If Range("J3").Value = "L" Then Range("Q2").Value = -5
End If



Application.EnableEvents = True

End Sub


Now here is what happens. Assuming I have race 1 to 10 for the win market in one tab, and equivalent place markets in another tab. I linked first tab to win sheet, then linked second tab to place sheet.

Place sheet goes through all the races once,, loading them into an array as it should do. Then the win sheet and place sheet start syncing and working in harmony However, when win sheet gets to race 10, it stays there and does not go any further (back to race 1), and the place sheet stays at race 9 and does not even jump to race 10.

At your mercy sir ?