Q2 question on looping

Please post any questions regarding the program here.

Moderator: 2020vision

Q2 question on looping

Postby brumbie » Sat Dec 08, 2018 8:36 am

I have been trying to add a bit of code at the end of a already in place worksheet_change code.
I have trawled through the past threads for days and I still can't get this damn thing to work. Basically I am using the record and playback worksheet that somebody so kindly offered up the other year to open each race of the day in the quick pick list and record the odds, volume etc at 11am each morning. I have been doing this manually with no problem by just clicking a macro that changes Q2 to -1. however whenever I try to incorporate a bit of code at the end of worksheet_change it invariably either skips a race or doesn't populate any of the columns from SP onwards to the right.My latest effort taken from the captain way back viewtopic.php?f=5&t=9800&hilit=Worksheet_Change


If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
Application.EnableEvents = False 'turns off events so we don't retrigger the code
Application.Calculation = xlCalculationManual 'turns off all calculations

If Range("Q2").Value = 1 Then
Range("Q2").Value = -1
End If

Application.EnableEvents = True ' events back on
Application.Calculation = xlCalculationAutomatic ' calculations back on

This guy seemed to have cracked it but unfortunately he didn't elaborate enough:
viewtopic.php?f=5&t=3107&p=15826&hilit=loop+through+quick+pick+list#p15826


Any suggestions guys?
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia

Re: Q2 question on looping

Postby Captain Sensible » Sat Dec 08, 2018 2:26 pm

BA sends data to excel at least twice in each refresh, one populates the market details and prices the other populates the additional columns like SP etc

Because the additional data gets sent after the price data you need to ensure your VBA runs when you actually want it to if you're using additional columns to trigger bets, with formulas that shouldn't be a problem as I assume BA picks up those details when it does the next refresh.

When you're using -1 to switch markets you need to make sure that the switching criteria is only met once or you will have -1 being sent twice or even more times especially if you rely on formulas to trigger it. Most of us will only trigger -1 into Q2 via vba as you'll then have complete control on when it fires, I simply set a variable to true or false , something like below so the -1 would only be sent once and I'd reset the Switchmarketvariable to false after switching to a new market

If "criteria" And SwitchMarketVariable=False Then
SwitchMarketVariable=True
Range("Q2").Value = -1
End If
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Q2 question on looping

Postby brumbie » Tue Dec 11, 2018 10:24 pm

Thanks Captain, I'm still working on it but I get your gist. If I get it going I'll put it up. :)
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia

Re: Q2 question on looping

Postby brumbie » Wed Dec 12, 2018 10:43 am

Think I've cracked it after many a manic hour:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("AT3").Value = -1 And Range("AU3").Value = False Then ' criteria to meet -1'
Range("AU3").Value = True 'SwitchMarketVariable to True'
Call CallNextMarket 'Q2=-1'
End If
Application.EnableEvents = True

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False
Range("AU3").Value = False ''SwitchMarketVariable to False'
End If
Application.EnableEvents = True


Working in practice, will try at 9pm tonight (11am GMT)

If it looks wrong let me know otherwise thank you for your time captain. :D
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia


Return to Help

Who is online

Users browsing this forum: No registered users and 33 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.