VBA - Skip a market not in play

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

VBA - Skip a market not in play

Postby mikey » Tue Aug 30, 2016 11:59 pm

I'm trying to skip a market which is not in play (us racing), I have some vba which I have wrong, can anyone point me the right direction?

This appears to skips all markets even if [I3] is a "Y" !


Code: Select all
If Range("I3").Value = "Y" Then
     
       GoTo xitnotinplay
       
        Else
       [q2] = -1
   
xitnotinplay:
        End If



Any help gratefully appreciated.


mike..
mikey
 
Posts: 29
Joined: Mon Aug 24, 2009 10:01 pm
Location: Suffolk

Re: VBA - Skip a market not in play

Postby bolpx001 » Wed Aug 31, 2016 11:08 am

I think it should be
If Range("I3").Value = "Y" Then

GoTo xitnotinplay

Else
Range("q2").Value = -1

xitnotinplay:
End If

I haven't tested it so can't be sure - best regards - Paul
bolpx001
 
Posts: 297
Joined: Sat Nov 19, 2005 4:30 am
Location: Dublin, Ireland

Re: VBA - Skip a market not in play

Postby mikey » Wed Aug 31, 2016 12:24 pm

Thanks Paul, The q2 value is going in , thats not the issue , the problem is its putting the q2 value in even if the i3 value is "Y" so i have something wrong with the checking of the value in the (i3 ) cell , or i have this code in the wrong place maybe?


here is the full code, its very messy.....

Public Sub Worksheet_Change(ByVal Target As Range)

Code: Select all
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
                   


Static InPlay As Variant
Static ConMet As Variant
Static MyMarket As Variant
Static Recorded As Variant
Dim counter As Currency

 


    'Clear cells on market start
    If [A1].Value = MyMarket Then

        GoTo Xitmymarket
        Else

        MyMarket = [A1].Value
        [q2] = ""



        [CC4] = False ' Recorded value reset to false
        [CC17] = False ' set recorded sp to false
        [CI5:CI30].Value = ""

Xitmymarket:

    End If

' If Range("I3").Value = "Y" Then
     
 '      GoTo xitnotinplay
       
 '      Else
'[q2] = -1
   
'xitnotinplay:
'End If

If [e2] = "Not In Play" And [F2] = "Closed" Then

[q2] = -1

End If











    If [cc16] = False Then ' if not inplay then skip this
        GoTo xitinplay
        Else ' when inplay copy the odds

        [CI5:CI30].Value = [F5:F30].Value

        [CC17] = True ' set the recorded trigger to true
        [CC4] = False

xitinplay:

    End If


    If [CC3].Value = True Then

        Call PandL
       

        'Set result saved to true
        [CC4].Value = True
        'set win/lost for next race
        [cc26].Value = [cc27].Value
        [q2] = -1


    End If


Application.EnableEvents = True
 
 End If

End Sub
mikey
 
Posts: 29
Joined: Mon Aug 24, 2009 10:01 pm
Location: Suffolk

Re: VBA - Skip a market not in play

Postby Captain Sensible » Wed Aug 31, 2016 2:23 pm

Haven't looked at your full code as racing's started but why is xitnotinplay: within the if else routine?

Code: Select all

If Range("I3").Value = "Y" Then
     
       GoTo xitnotinplay
       
        Else
       [q2] = -1
   
xitnotinplay:
        End If


is exactly the same as


Code: Select all
If Range("I3").Value = "Y" Then
     

       
        Else
       [q2] = -1
   

        End If


If you're jumping out of a loop you'd need to make sure no other code is interferring with you putting -1 in the Q2 cell i.e. your other code isn't entering data into Q2 after you've put in -1. If you want to move markets I'd be inclined to have the code like this


Code: Select all

If Range("I3").Value = "Y" Then
     
   
       
        Else
       [q2] = -1
    GoTo xitnotinplay
   

        End If


## rest of your coding with xitnotinplay: at the end so effectively the only code active is entering -1 in Q2


xitnotinplay:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: VBA - Skip a market not in play

Postby mikey » Wed Aug 31, 2016 3:38 pm

Thanks captain , it is very messy, ill change it and hopefully it will work.
mikey
 
Posts: 29
Joined: Mon Aug 24, 2009 10:01 pm
Location: Suffolk

Re: VBA - Skip a market not in play

Postby Captain Sensible » Wed Aug 31, 2016 4:58 pm

No problem, it can be a pain getting eerything to flow as you want, I try and put all my routines into modules just for ease of reading and being able to edit sections as when I need to. The bottom section of most of my sheets is simply as follows and I just use goto Switch_Market in my If/elses if I want to move markets.


Code: Select all


Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
    Worksheets("Sheet1").Range("Q2").Value = -1
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: VBA - Skip a market not in play

Postby bolpx001 » Wed Aug 31, 2016 5:30 pm

Just out of curiosity Captain, if you enter -1 in q2 and the market changes does BA clear q2 or do you need to do it yourself: Also for what it is worth if I spend an hour programming some VBA code it will usually take another two hours again I get it to do what I want and clear the bugs out of it. :(
best regards Paul
bolpx001
 
Posts: 297
Joined: Sat Nov 19, 2005 4:30 am
Location: Dublin, Ireland

Re: VBA - Skip a market not in play

Postby Captain Sensible » Wed Aug 31, 2016 6:11 pm

BA clears the Q2 cell on each refresh that's why it's important that your code doesn't accidentally enter -1 in Q2 again of it'll keep skipping markets.

The amount of hassle I've had with VBA code doesn't bear thinking about , I really wish I could spend some time actually learning it but probably like yourself it's generally trial and error for me. As soon as I get something working I no longer have any intrest in tweaking or prettying up the code.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: VBA - Skip a market not in play

Postby bolpx001 » Thu Sep 01, 2016 10:41 am

Thanks Captain :)
bolpx001
 
Posts: 297
Joined: Sat Nov 19, 2005 4:30 am
Location: Dublin, Ireland


Return to Discussion

Who is online

Users browsing this forum: Bing [Bot] and 34 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.

cron