Code Help Please

Find a developer for your Excel triggered betting needs and advertise your development service here.

Moderator: 2020vision

Code Help Please

Postby Steve Voltage » Sun Aug 02, 2015 8:21 am

I need some simple code that will put the trigger -1 into cell Q2 if cell V5 is not blank.

I've searched the gruss forum but no joy and excel/vba code hurts my brain.

I can do the above easily enough with a formula but the formula disappears when -1 is entered in Q2.

Any help please Happy
Steve Voltage
 

Re: Code Help Please

Postby Captain Sensible » Sun Aug 02, 2015 1:45 pm

Dunno if you already have VBA running on your sheet but it's as simple as adding the code below if you do.


Code: Select all
If Range("V5").Value <>"" Then Range("Q2").Value = -1



You'd probably find it's better to add whatever formula you have in V5 as a VBA routine tbh as it'd avoid situations where the -1 my get duplicated
User avatar
Captain Sensible
 
Posts: 2687
Joined: Sat Nov 19, 2005 2:29 pm

Re: Code Help Please

Postby Steve Voltage » Mon Aug 03, 2015 9:06 am

Hi Captain thanks for the code. :)

Unfortunately I cannot get it to work.
A couple of years ago I started to pick up the basics of code but I haven't used excel for awhile and although I find formulas fairly easy code remains a mystery to me.

Do I need to add worksheet calculate, application update or something?

Also good to see a name i recognise still here. You managing to still make a profit or just on here for fun/an interest?

Cheers,

Steve
Steve Voltage
 

Re: Code Help Please

Postby Captain Sensible » Mon Aug 03, 2015 2:08 pm

I thought you already had VBA on the sheet, if not use something like the code below, no idea how you populate V5 but be aware if V5 is noot clear it will keep moving markets that's why it's usually best to control all your market moving code with VBA

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
    Application.Calculation = xlCalculationManual
    With Target.Parent
   
If .Range("V5").Value <> "" Then .Range("Q2").Value = -1
   
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
End Sub
User avatar
Captain Sensible
 
Posts: 2687
Joined: Sat Nov 19, 2005 2:29 pm

Re: Code Help Please

Postby Steve Voltage » Mon Aug 03, 2015 7:18 pm

Mr Sensible that works a treat thank you very much. Very much appreciated and the best of luck to you sir! 8)
Steve Voltage
 

Re: Code Help Please

Postby Steve Voltage » Mon Aug 03, 2015 8:25 pm

One last question if i may. I am trying to set up a counter which counts how many times the last matched price changes.

I can do this with the following code that counts every time O5 is changed and counts in Y5:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("O5")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value <> "" Then
With Target.Offset(0, 25)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Trouble is this code only works when A1 is change manually. How would i get this code to work with BA and also to be incorporated with the other Worksheet_Change code so not to cause an error?

Many thanks,

Steve
Steve Voltage
 

Re: Code Help Please

Postby Captain Sensible » Mon Aug 03, 2015 11:55 pm

Don't know what you mean by "only works when A1 is change manually" and never used intersect personally. Also my sheet shows V5 as the Averaged matched Odds so isn't ever empty so hard for me to recreate what you're trying to do.

The code below adds in a counter for when O5 changes, basically it'll store the value in a variable, if that changes it'll add 1 to Y5. It'll also clear those cells when the market move, just remove those lines if that's not what you need.

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Static LastPrice As Variant


    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
    Application.Calculation = xlCalculationManual
    With Target.Parent
   
   
   
If LastPrice <> .Range("O5").Value Then
LastPrice = .Range("O5").Value
.Range("Y5").Value = .Range("Y5").Value + 1
End If


If .Range("V5").Value<>"" Then
.Range("Q2").Value = -1
.Range("Y5").Value = ""
LastPrice = 0
End If


   
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
End Sub
User avatar
Captain Sensible
 
Posts: 2687
Joined: Sat Nov 19, 2005 2:29 pm

Re: Code Help Please

Postby Steve Voltage » Thu Aug 13, 2015 7:33 am

Captain Sensible wrote:I thought you already had VBA on the sheet, if not use something like the code below, no idea how you populate V5 but be aware if V5 is noot clear it will keep moving markets that's why it's usually best to control all your market moving code with VBA

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
    Application.Calculation = xlCalculationManual
    With Target.Parent
   
If .Range("V5").Value <> "" Then .Range("Q2").Value = -1
   
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
End Sub




Indeed you are quite correct Captain. I didn't realize at first but sometimes the code will go through the picklist with no problem but other times it will miss a selection by refreshing too quick. I have no idea why and it drives me mad!

Basically this is all i need:

When V5 is populated -1 will be entered in Q2. I have tried to write my own code using the example sheets provided by after hours i get nowhere. Vba is obviously not for me, i just cannot grasp it.
Steve Voltage
 

Re: Code Help Please

Postby Steve Voltage » Fri Aug 28, 2015 9:50 pm

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
If Sheet1.Cells(9, 28) = "0" Then
Sheet1.Cells(2, 17) = "2"
Else
Sheet1.Cells(2, 17) = "-1"
End If
End Sub


Ok i am getting somewhere with this but why does this the above code skip every other race in the quick pick list?
Steve Voltage
 


Return to Find an Excel developer

Who is online

Users browsing this forum: No registered users and 3 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.