help with Auto beting football markets

[mouse]

Doh. Scrolled all the way down and had Reults ticked as an extra column so ****** <> 5 now works fine. Thnx

Any reason why this vba does not work a write to trigger cell Q5 e.g.
If .Range("T6").Value = "" Then
Range("Q6").Value = “BACK”

Does not work for me.

[mouse]

Doh. Scrolled all the way down and had Reults ticked as an extra column so ****** <> 5 now works fine. Thnx for help again. V much appreciated.

Any reason why this type vba does not work a write to trigger cell Q5 e.g.
If .Range("T5").Value = "" Then
Range("Q5").Value = “BACK”

Does not work for me.

[Captain Sensible]

No idea why it's not working. Probably need to see it as part of the whole code. Is it all on one line ,if not there's an End If missing. Are events currently enabled? Sometimes events gets turned off by coding errors and you need to ensure they get put back on. That's why a lot of code you see has On Error traps to direct it to enable events.

[Captain Sensible]

Here's a sheet I used to use to test code. Basically there a button called Refresh that when pressed will change data in 5 column AA1:AE1, that mimics a refresh from BA so you can't try out your code without it being linked to BA and worrying about it firing off bets.

TestCode.zip

(17.95 KiB) Downloaded 1336 times

The button fires off a sub routine in Module 1 that simply enables events (in case they've been turned off) and then sticks random numbers in AA1:AE5.

So you can now change any fields manually to whatever values Not In Play to In Play etc then click the button to fire off your VBA to see if it's working as you want before going live.

Gruss won't let me add macro enabled files so I've had to zip it.

Otherwise simply make up your own file with the code below under Sheet 1

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As String
    Static counter As Long

    If Target.Columns.Count <> 5 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    On Error GoTo xit



    With Target.Parent

'your code in here
MsgBox "hello"

    End With



xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    End Sub


Add a button to that sheet and link it to a sub code as below

Code: Select all

Sub refresh()
Application.EnableEvents = True 'Turn on events in case switched off
Range("AA1:AE1").Value = Rnd()
End Sub


[mouse]

Again thank you for your time on this. Since my previous post got into all sorts of problems with the counter code - many games deleted/not deleted etc. So decided to give your 10 sec loop code a try. This i have adapted to delete games i dont want or in play, or closed etc. and it works without fault. Have now been attempting to inc code to fire off a bet such as

If .Range("T6")value="" And other criteria Then
:Range("Q6")value="LAY"

The odds and stake are set in R and s cells in the worksheet. The thing is i have tried placing this within the "loop" and whatever i try is ignored. Any ideas where it should go in below - ?

Code: Select all

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

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

On Error GoTo xit

With Target.Parent

If .Range("AA1").Value = "" Then .Range("AA1").Value = Now()
.Range("AA2").Value = DateDiff("s", .Range("AA1").Value, Now())
If DateDiff("s", .Range("AA1").Value, Now()) >= 10 Then

If .Range("J3").Value = "L" Then
.Range("Q2").Value = -5
Else
.Range("Q2").Value = -1
End If

If .Range("E2").Value = "In Play" Or .Range("F2").Value = "Closed"  Then
.Range("Q2").Value = -8
End If

.Range("AA1").Value = Now()
End If
End With
xit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

[Captain Sensible]

Might be worth you adding that refresh button to your sheet so you can test things out quicker and track any errors with your syntax quickly. Also comment out the line

Code: Select all

On Error GoTo xit

for now just by adding a single quotation mark

Code: Select all

 'On Error GoTo xit

that way any errors in your code will get highlighted.


Without knowing where you place and what criteria it's hard to second guess why it's not working . Don't know if you typed it out quickly but the syntax in the code you stuck up is all wrong

You need to type .Range("T6").Value not .Range("T6")value, and .Range("Q6").Value not :Range("Q6")value. The .Value means we take the value of that cell, many other operators are used in VBA like .Format with would format the cell etc. So your code would be something like

On one line

Code: Select all

If .Range("T6").Value="" And other criteria Then .Range("Q6").Value="LAY"

Over a few lines

Code: Select all

If .Range("T6").Value="" And other criteria Then
.Range("Q6").Value="LAY"
End If

[Captain Sensible]

As to where you place the LAy code it's up to you basically you don't want it inside some IF loop that might stop it from ever firing but otherwise the start or end of the With.Target.Parent seems fine. Just remember you'd also need something to record when you've fired a bet otherwise it might fire off extra bets each time it loops back to the market.

[Captain Sensible]

If you only wish to place one bet it might be worth using -8 to delete that market from the Quick pick list too

[mouse]

Ok. will give that a go. Many thnx