Excel/VBA Help

[ktng]

I've got a spreadsheet that does triggered betting and it's working fine, but what I would like to do is copy any selections that it makes to another sheet.

I have a sheet with all my selections in that is used as a lookup on the main sheet, so what I would like to end up with is a sheet called say selection history that would just contain any of my selections that met the criteria (at the moment this is just race must have more than specified minimum number of runners
and selections must be below a maximum price). I know I can get this information from betfair for any bets actually placed, but I would also like to use this to test out systems and maybe even some tipsters to see what would get selected and actual betfair prices say 2minutes before the off.

I've seen some posts on here that imply that this is possible, but I can't find any code posted to do this and I have no idea how to do it in VBA so would appreciate some help. TIA

[Mitch]

Does your spreadsheet contain any vba at he moment or is it all formulas?

[ktng]

Hi Mitch,

It's all formula at the moment.

[Mitch]

Ok, so what we need then is a "rule" for vba to decide when a selection has been made, for example, a particular cell will have a particular word in it, or something similar. i.e. when col.Q has BACK or LAY in it?

I take it your sheet places a bet when a selection is made. Do you want to record all selections, or just when bets are matched (or maybe that's the same thing?)

[ktng]

Yes col Q has LAY in it when it finds a race with a horse from my lookup list that satisfies the criteria.

Yes it does place bets for any selections that matches the criteria. I would like to record all selections, so that I can also monitor if I get any bets that don't get matched.

[Mitch]

Once LAY appears in col.Q, does it stay there until it gets cleared for the next race, or do you clear it yourself with the formula.

The reason I ask is we can probablt get the sheet to record how much of your bet is matched as well if that would save you a manual job.

Is there ever more than 1 selection per race?

[ktng]

Yes LAY stays in col Q until it gets cleared for the next race. In theory there could be more than 1 selection per race.

[Mitch]

I've had a quick play, and although it's not intentionally wrong I usually find there's silly mistakes in any code the first time it's run (maybe it's just me) but I have no way to test it as I have no sheets that work in the way that yours does.

You need to add a new worksheet and call it "Bets", then right-click the tab of the sheet that BA puts the data into and choose 'view code'. Copy the code below and paste it into the window that's just appeared. On the first row of your new worksheet put the headings.......
Event, Selection, Action, Odds, Stake, Bet Ref, Bet Time, Matched Odds, Matched Stake........
and format them however you want to.

If there's any errors in it, you'll get an error message come up and an option to 'End' or 'Debug'.
Could you click the Debug button and let me know which line is highlighted in yellow, and what the error message said. Then you need to click the STOP icon (a blue square) on the toolbar before Excel will accept any more data from BA.

I hope you don't mind being the Guinea-Pig for this, but no bets can be placed if it does go wrong. The worst that could happen is it records your bets wrong, doesn't record them at all, or prevents your bets being placed.

Let me know how you get on.

Code: Select all

Option Explicit
Option Compare Text

Dim lastrow As Integer, rw As Integer
Dim i As Integer, j As Integer
Dim betref As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Columns.Count <> 16 Then Exit Sub
   
    Application.EnableEvents = False
    lastrow = Target.Rows.Count
   
    For i = 5 To lastrow
        If Cells(i, 17) = "LAY" Then
            betref = Cells(i, 20)
            CopyData
        End If
    Next i
   
    Application.EnableEvents = True
   
End Sub

Sub CopyData()

    With Sheets("Bets")
   
        If WorksheetFunction.CountIf(.Range("F:F"), betref) > 0 Then
            rw = .Range("F:F").Find(What:=betref, After:=.Range("A1"), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False).Row
            Else
            rw = .Range("A65536").End(xlUp).Row + 1
            .Cells(rw, 1) = Range("A1")
            .Cells(rw, 2) = Cells(i, 1)
        End If
       
        Range(Cells(i, 17), Cells(i, 23)).Copy Destination:=.Cells(rw, 3)
       
    End With
   
End Sub


[ktng]

Hi Mitch,

No problem being a Guinea-Pig. I very much appreciate your help.

The horse racing had finished, so loaded the greyhounds up into BA and got the following error. Runtime error '13' Type mismatch and it highlighted the following code:-

rw = .Range("F:F").Find(What:=betref, After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row

[Mitch]

Shame, I didn't think it would work straight away. I might have to invent a sheet myself to test it with. If you're about this afternoon and want to try my stabs in the dark before I can test it at home.........

In the line that's thrown the error, change .Range("F:F") to .Cells

If it still throws up the same error then try changing the line (further up) If Cells(i, 17) = "LAY" Then to If Cells(i, 17) = "LAY" And Cells(i, 20) <> "PENDING" Then

Like I said, just my stabs in the dark, and there'll probably be something else wrong if this does work, but I'll get there in the end.

[ktng]

Hi Mitch,

I made the first change and it no longer gives an error, but it also doesn't copy anything to Bets worksheet. On this attempt I didn't have trigger betting enabled as it looks like the code should copy selection to Bets even if you don't get a matched bet, but I could have got that wrong.

[Mitch]

You're right, it should copy selections regardless of whether they get matched or not.

Did you make the change after the error had happened again? If so, then the code might not even have been running, as the code disables event handlers at the start and wouldn't enable them again if the code didn't reach the end.

If you know a little vba you could run through the code step by step in break mode, failing that I will do it next time I am free at home.

[ktng]

No I changed the code before I hooked it up to BA.

I'm afraid my knowledge of VBA is pretty much zero - could work out that the IF ELSE was looking for matched bets and that's about it

If it helps I can send the spreadsheet I'm using.

[Mitch]

PM sent.

[ktng]

Replied to the PM.