Macro code after market refresh

[vanbuuts]

Hi all, when I select the -1 option in cell Q2 it selects the next event in my quick pick list.

The problem is, this takes a few seconds to populate in Excel.

I have code that runs based on the odds criteria. Is there a way to run this code ONCE the market has refreshed in Excel? If I tag it onto the end of the code that places the -1 in cell Q2 it runs instantly and doesn't give the refresh time to complete

[Captain Sensible]

Not too clear what you're trying to do but to ensure code runs when I want it I set flags within VBA after certain actions have completed and then just use an IF statement to kick off other routines when all criteria has been met.

For example I think the extra columns ,SP etc don't get sent on the first excel refresh so once a new market is selected my code resets all variables then simply starts counting the refreshes by adding 1 to a variable, once that's reached 2 my other code can run,

[vanbuuts]

Hi, thanks for the reply. SOrry if I have no explained myself clearly.

Using the example - with UK and Ire races in the quick pick list.

I want my Excel sheet to check the race to see if there is a horse running at odds of 2.0 (Evens) or less. If there is, then a lay bet needs to be triggered. This part is fine, I have the code to handle this.

What I then want to do, is once the race has finished or bet has been matched (irrelivant really) is load the next race in the pick list and look to see if there is a horse running with odds of 2.0 or lower. If it does, then run the macro for the lay, if it doesn't then load the next race (by putting a -1 in cell Q2)

THE PROBLEM - here is that when the code puts a -1 in cell Q2, the next part of the code (searching for odds of <2.0) runs BEFORE the next race info has loaded into my excel sheet. So what ends up happening is the macro just loops through each race until it gets to the last race of the day - never stopping.

[Captain Sensible]

When you say "once the race has finished or bet has been matched (irrelivant really) " I'm not sure are you betting in running too or just pre-off and does it matter how close to the off you bet? Only reason I'm asking if cos obviously BA has built in options to move between races close to and after the off. If your sole criteria is just having something 2 or under and the time doesn't matter, I'd just set it to lay the 2 and under and lay everything else at 2 because odds flip flop and fat fingers can cause alsorts of havoc.

To be fully auto you need to set criteria for when that -1 gets sent rather than just have it at the end of the Worksheet_Change/Calculate routine, just add a separate macro for the market change and have it called at the end of the lay bet routine or called if your checking routine says no lays.

[vanbuuts]

Hi, sorry the bets are always place in Play by my macro code.

I will have a play around with adding the code at the end of my existing code.

[Captain Sensible]

If you're only betting In Play I'd consider using BA's own Auto Select Options like At specified time before the off as there doesn;t seem any need for you to move to markets early and delayed meetings could mean you'd miss most of the days races.

Otherwise just do something simple to log when the market goes inplay , count a certain number of refreshes to avoid things like false starts, then reset variables for the next race and put your -1 in Q2 to move on. Haven't tested it but hopefully give you some ideas on using flags etc to monitor where you are, just change the Range("AA2").Value > 50 value depending on your refresh rate on how long you want to wait

Code: Select all

    If Range("E11").Value = "In Play" Then
    Range("AA1").Value = "OFF"
    Range("AA2").Value = Range("AA2").Value + 1
    ElseIf Range("E11").Value = "Not In Play" Then  'in case of false starts etc
    Range("AA1").Value = ""
    Range("AA2").Value = ""
    End If

If Range("AA2").Value > 50 And Range("AA1").Value = "OFF" And Range("F11").Value = "Suspended" Then

Range("AA1:AA2").Value = "" 'reset flags
 Range("Q11").Value = -1

End If

[Captain Sensible]

Just change E11 to E2 and F11 to F2, I always sent data to A10 to have the top of the sheet free for settings and am used to In Play being sent to E11 not the default E2

[Captain Sensible]

And the Q11 to Q2

Surely it's about time we were allowed to edit posts for a short period

[vanbuuts]

Hi CS, thanks for the replies. Will try and get my head around what you are saying.

My sheet has become quite complex and muddled (don't they always). I think I need to simplify it down and then try and get my head around the move to next market routine

[Captain Sensible]

Same here plenty of my sheets have so much redundant code in them I'd be better off starting from scratch. These days I try to comment my code so I know what it's doing and try and keep most routine in macros and just use Call Whatever_Macro to get them to run from my worksheet_change routines. Also worth you doing flow charts beforehand just so you can get the correct order of things running.

I tried the code I posted up and it works fine, might be a starting point for you. But like I said earlier as soon as you start relying on your code to move things along you really need to start looking at all the scenarios that can stop your code like Betfair errors stopping a market going in running,delayed meetings etc etc I still try and use BA's select market before the off if I can just cos I'd rather miss the odd late race than a fulls days racing due to a delayed meeting, a TBP market slipping in etc

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
   
    Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event

      If Range("E2").Value = "In Play" Then
        Range("AA1").Value = "OFF"
        Range("AA2").Value = Range("AA2").Value + 1
        ElseIf Range("E2").Value = "Not In Play" Then  'in case of false starts etc
        Range("AA1").Value = ""
        Range("AA2").Value = ""
        End If

    If Range("AA2").Value > 50 And Range("AA1").Value = "OFF" And Range("F2").Value = "Suspended" Then

    Range("AA1:AA2").Value = "" 'reset flags
     Range("Q2").Value = -1

    End If




   
Xit:
 
    Application.EnableEvents = True 'Turn on events again
End Sub