Capture Range on Going In Play

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

Moderator: 2020vision

Capture Range on Going In Play

Postby AndrewP » Mon Sep 02, 2019 8:03 pm

Could some kind person help me out with code that would achieve the following please,

On cell E2 changing to "In Play"
Copy the range A1:Z40
Paste as values to end of 'sheet3' in same workbook (i.e. to next blank row, column A)
AndrewP
 
Posts: 39
Joined: Sat Nov 03, 2007 10:52 am

Re: Capture Range on Going In Play

Postby MollyMoo » Tue Sep 24, 2019 11:45 am

Try this in the VBA editor of the worksheet you want to copy from:

Code: Select all
Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Address = "$E$2" And Target.Value = "In Play" Then
Dim LastRow as Long
LastRow = Worksheets("Sheet3").Cells(Rows.Count,1).End(xlup).Offset(1).Row
'Assumes you are copying from "Sheet1". Change if different.
Worksheets("Sheet1").Range("A1:Z40").Copy Worksheets("Sheet3").Cells(LastRow,1)
Application.CutCopyMode = False
End If
End Sub
MollyMoo
 
Posts: 90
Joined: Mon Sep 11, 2017 9:31 am
Location: UK

Re: Capture Range on Going In Play

Postby AndrewP » Fri Sep 27, 2019 5:12 pm

Thanks for your reply MollyMoo.

I tried the code but got a run-time error: 13 'type mismatch' and the debugger was highlighting the second line.
AndrewP
 
Posts: 39
Joined: Sat Nov 03, 2007 10:52 am

Re: Capture Range on Going In Play

Postby MarkT300 » Tue Oct 01, 2019 7:07 pm

Hi, I can only work out how to do this with a helper cell in excel. In this case i'm using AA1. If you are using this cell for something else just change it in the code.

Hope this works, let me know if not.

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

Dim r1, r2 As Range
Dim PasteRow As Long

If Not Intersect(Target, Range("E2")) Is Nothing Then

    If Range("AA1").Value = Range("E2").Value Then
    'do nothing
   
    Else
   
    Range("AA1") = Range("E2")
       
        If Range("E2") = "In Play" Then
       
            'copy/paste
            Set r1 = Sheets("Sheet1").Range("A1:Z40")
            PasteRow = Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, "A").End(xlUp).Row
            Set r2 = Sheets("Sheet3").Range("A" & PasteRow)
            r1.Copy r2
        End If
    End If
End If

End Sub
MarkT300
 
Posts: 1
Joined: Tue Oct 01, 2019 8:16 am

Re: Capture Range on Going In Play

Postby MollyMoo » Fri Oct 04, 2019 6:26 pm

Sorry. Try this:

Code: Select all
Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Address = "$E$2" Then
If  Target.Value = "In Play" Then
Dim LastRow as Long
LastRow = Worksheets("Sheet3").Cells(Rows.Count,1).End(xlup).Offset(1).Row
'Assumes you are copying from "Sheet1". Change if different.
Worksheets("Sheet1").Range("A1:Z40").Copy Worksheets("Sheet3").Cells(LastRow,1)
Application.CutCopyMode = False
End If
End If
End Sub
MollyMoo
 
Posts: 90
Joined: Mon Sep 11, 2017 9:31 am
Location: UK

Re: Capture Range on Going In Play

Postby AndrewP » Sun Oct 06, 2019 7:12 pm

Thank you MollyMoo and Mark for your replies.

Both sets of code work in a sheet where I manually alter the contents of cell E2 not connected to BA. However, they do not work when BA is logging to the sheet even if I include the lines,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
...
Application.EnableEvents = True
End Sub
AndrewP
 
Posts: 39
Joined: Sat Nov 03, 2007 10:52 am

Re: Capture Range on Going In Play

Postby Captain Sensible » Mon Oct 07, 2019 11:24 am

Are you trying to copy the odds at the off or continue to capture all the inplay prices of a race.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Capture Range on Going In Play

Postby AndrewP » Mon Oct 07, 2019 2:54 pm

I'm trying to capture the odds, BSP and volumes at the off CS.
AndrewP
 
Posts: 39
Joined: Sat Nov 03, 2007 10:52 am

Re: Capture Range on Going In Play

Postby Captain Sensible » Mon Oct 07, 2019 7:45 pm

Try something like this see if if works, it may need tweaking depending on your refresh rates as BA does two data refreshes one for the price data and one for the additional columns like BSP etc

Without knowing what extra columns you use it's hard to capture at the right time as this will capture when the E2 indicator changes to In Play but it's likely BA will send the updated BSP data after that refresh. Basically it just means the line If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub needs to be changed so it captures when the additional columns are sent to the sheet rather than when the price data is sent.






Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer, y As Integer
x = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
y = Worksheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

    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
 
     
           
        If Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("T1").Value Then GoTo xit
   
   
 
        If Worksheets("Sheet1").Range("E2").Value = "In Play" Then
       
                Worksheets("Sheet1").Range("T1").Value = Worksheets("Sheet1").Range("A1").Value
                Worksheets("Sheet3").Range("A" & y + 1 & ":Z" & x + y).Value = Worksheets("Sheet1").Range("A1:Z" & x).Value
               
        End If

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

Re: Capture Range on Going In Play

Postby Captain Sensible » Mon Oct 07, 2019 7:50 pm

If you want to see the data BA sends to excel just put this in a sheet

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
 Debug.Print Target.Address
End Sub


And look at the immediate window and it'll show the data being sent, something like

$A$1:$P$17
$T$5:$Y$17

This shows the columns of data being sent, $A$1:$P$17 is 16 columns so the line

If Target.Columns.Count <> 16 Then Exit Sub

Means we exit the sub if we don't get 16 columns of data, you should change that 16 to however many columns are being sent in the second data set ,$T$5:$Y$17 in the example above i.e. 6 you may have more.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Capture Range on Going In Play

Postby AndrewP » Tue Oct 08, 2019 1:10 am

Thank you for the code and for such a comprehensive explanation CS.

I'll be out during the day tomorrow but I'll be back for evening racing. I'll give it a try then and report back.

Andrew
AndrewP
 
Posts: 39
Joined: Sat Nov 03, 2007 10:52 am

Re: Capture Range on Going In Play

Postby Captain Sensible » Tue Oct 08, 2019 4:43 pm

No problem, like I say you just need to change the line

Code: Select all
 If Target.Columns.Count <> 16 Then Exit Sub


To however many columns get sent in the second data set from BA, will most likely be either 6 or 7 (if data is being sent to the Z column) so

Code: Select all
 If Target.Columns.Count <> 7 Then Exit Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Capture Range on Going In Play

Postby AndrewP » Thu Oct 10, 2019 4:45 pm

That works CaptainS, thank you so much.

I get 'run-time error '9' subscript out of range' if there is another spreadsheet open in the same instance of Excel. Any idea why that would be? Debug is highlights the line

x = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Easily circumvented by running the BA sheet in its own instance of excel.
AndrewP
 
Posts: 39
Joined: Sat Nov 03, 2007 10:52 am

Re: Capture Range on Going In Play

Postby Captain Sensible » Thu Oct 10, 2019 10:10 pm

Probably occurring when the other sheet is in focus ie selected as it also contains a sheet1 maybe.

Probably just needs amending so all the VBA code is running on that workbook only by using thisworkbook or using with target etc . Not at my pc now so gave a look at options tomorrow.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Capture Range on Going In Play

Postby Captain Sensible » Fri Oct 11, 2019 1:19 pm

Maybe try this, I haven't been able to test it and was just a find and replace but should still work.

Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer, y As Integer
Dim Wb As Workbook
Set Wb = ThisWorkbook
x = Wb.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
y = Wb.Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

    If Target.Columns.Count <> 7 Then Exit Sub  'Amend the number to the cells that change with the BSP data
        Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
        Application.Calculation = xlCalculationManual
 
           
        If Wb.Sheets("Sheet1").Range("A1").Value = Wb.Sheets("Sheet1").Range("T1").Value Then GoTo xit
   
   
 
        If Wb.Sheets("Sheet1").Range("F2").Value = "Suspended" Then
       
                Wb.Sheets("Sheet1").Range("T1").Value = Wb.Sheets("Sheet1").Range("A1").Value
                Wb.Sheets("Sheet3").Range("A" & y + 1 & ":Z" & x + y).Value = Wb.Sheets("Sheet1").Range("A1:Z" & x).Value
               
        End If

xit:
   
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True 'Turn on events again
End Sub



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

Next

Return to Find an Excel developer

Who is online

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