Capture Range on Going In Play

[AndrewP]

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)

[MollyMoo]

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

[AndrewP]

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.

[MarkT300]

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

[MollyMoo]

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

[AndrewP]

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

[Captain Sensible]

Are you trying to copy the odds at the off or continue to capture all the inplay prices of a race.

[AndrewP]

I'm trying to capture the odds, BSP and volumes at the off CS.

[Captain Sensible]

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

[Captain Sensible]

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.

[AndrewP]

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

[Captain Sensible]

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

[AndrewP]

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.

[Captain Sensible]

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.

[Captain Sensible]

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