VBA copy not happening

[G78]

Hi

I'm loving your software (which I currently have on trial) and have been trying to automate in Excel.

The problem I have is that when I use my Excel sheet live, my VBA copy/paste doesn't work. I'm guessing this is due to the way it refreshes, so it therefore removes my copied data. How do I go about performing a copy and paste on my data?

In case there's an easier way, what I'm doing is when a bet is matched, I'm copying some of those cells elsewhere. It originally watched for a change in the "Bet ref" cell, but now it watches for greater than 0 in the "Avg matched" cell.

Thanks

[Captain Sensible]

Without seeing the code you're using it's hard to second guess why it's not working. There are plenty of code snippets on the forum for copying data with VBA. Have a search around you should be able to find some code to tweak to your needs or even consider using the MyBets sheet to track if it's bet data you're logging.

[MarkRussell]

Hi,

Ok, so when you have Betting Assistant linked to Excel it will update a range of cells on each refresh, i.e. these cells will be overwritten.

The range is dependent on number of runners in the linked market but the number of columns is fixed at 16 i.e. A to P assuming your origin cell is A1 when you initially start logging.

Additionally there are the 3 trigger columns Q, R and S plus any additional data columns that you may have selected.

But you can copy data using VBA to unused columns in your spreadsheet.
The best place to put the VBA is in the Worksheet_Change event and then check for when the block of 16 columns is updated, i.e. on each refresh.
So we recommend putting the code in the following Sub:-

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    IF Target.Columns.Count = 16 Then

    .....put your code here......

   End IF
End Sub

Regards,
Mark

[G78]

Wow, that's some prompt replying - I expected a few days of waiting - thanks!

I don't really know what I'm doing in VBA in truth, I can write basic code in other languages, but VBA is new.

The code I'm using basically copies the matched odds to a new cell. "Z". I then hope to delete the contents of this cell once it goes to the next race. I'm hoping to do this by checking the time remaining and if it's greater than 100 seconds to blank V5:V20, but this addition appears to cause Excel to stop working once the cells have been blanked.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("T3").Value > 100 Then
        Range("Z5:Z50").ClearContents
    End If
    Dim KeyCells As Range
   
    Set KeyCells = Range("V5:V20")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        If Range("V" & Target.Row).Value > 0 Then
            Range("V" & Target.Row).Copy
            Sheet1.Range("Z" & Target.Row).PasteSpecial xlPasteValues
        End If
    End If
    Application.CutCopyMode = False
End Sub

The above code works fine when not "live", once it's running nothing happens. I've actually modified it from some other code, for testing purposes, as what it actually did originally was watch for a bet ref, then copy Q to X within that row to a new sheet.

@Captain Sensible, thanks. I have looked around, but being a bit of an ignoramus, much of what I've found has made little sense. I'll venture further and try some things.

@MarkRussell Thanks, with the 16 columns, can that become 24 to take into account the details of the matched bets?

[G78]

T3 in the above is simply the time remaining in seconds until the start. Thanks

[Captain Sensible]

I'm sure your code's working fine manually but you need to remember when Betting Assistant sends data to excel it sends it in a block not as individual cell updates.

When you do things manually you'll simply be updating one cell, so say you amend V10 then the target row will be 10. When BA sends the block of data the block of data that intersects your set range will be T5 to X and the last cell, so say 5 runners T5:X20. The target row that VBA will pick out is now 5 so when the code you posted runs the only cell check is V5. Hopefully that makes some sense to you?

What most people do is simply loop thru the data range you want to query. If you want to go the extra mile for speed you'd grab the portion of the sheet you wish to amend into an array , amend any cells in memory then dump back to the excel sheet in one pass rather than writing each cell individually.

I can post up some exaples if any of that makes sense to you

[G78]

Some of that makes sense, yes. Other pats not so much, but if you could share some example code, I'd be grateful, thanks.

Strangely I was testing a moment ago and have on occasion (seemingly at random) I managed to get it to copy the matched odds.

[G78]

And if you have any examples of how to work with the trigger cells in VBA please, I'd also appreciate it as I can see working with them as a formula can be problematic.

Thanks

[Captain Sensible]

Some of that makes sense, yes. Other pats not so much, but if you could share some example code, I'd be grateful, thanks.

Strangely I was testing a moment ago and have on occasion (seemingly at random) I managed to get it to copy the matched odds.

When you use Worksheet_Change it's simply looking for a change on the sheet, there are a few other Worksheet_ options like Worksheet_Calculate or even Worksheet_SelectionChange you can use that all look for different changes. Because BA is sending the data as a block it's treated as one worksheet change and the data block that intersected your range V5:V20 would be T5:X? the Target.Row value that gets picked out of that block of cells is simply the first row it comes across i.e 5 from T5.

I'd guess when you were testing a moment ago the cell that got copied was V5. I'll stick up an example a bit later

[G78]

Correct, it was V5 and only V5. Seems to then have a bit of a strop about it as well. Thanks for your time.

[Captain Sensible]

I've tweaked your code below and added comments to the lines I added. Haven't tested it so no idea if it actually works as racing's about to start

Code: Select all

Option Explicit



    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
   
       
        If Range("T3").Value > 100 Then
            Range("Z5:Z50").ClearContents
        End If
       
        Dim KeyCells As Range
        Set KeyCells = Range("V5:V20")
        Dim lastrow As Long, i As Long 'declare some variables
        lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ' get last row of runner data for our loop
       
     
       
        If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
       
        For i = 5 To lastrow
       
        If Range("V" & i).Value > 0 Then Range("Z" & i).Value = Range("V" & i).Value ' copy V to Z if V > 0
           
           
        End If
        Next i
       
       
        End If
       
       
       
    Application.EnableEvents = True 'Turn on events again
    End Sub

[Captain Sensible]

Should be as below, I'd forgotten to remove one of your End if's

Code: Select all


Option Explicit

    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
   
       
        If Range("T3").Value > 100 Then
            Range("Z5:Z50").ClearContents
        End If
       
        Dim KeyCells As Range
        Set KeyCells = Range("V5:V20")
        Dim lastrow As Long, i As Long 'declare some variables
        lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ' get last row of runner data for our loop
       
     
       
        If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
       
        For i = 5 To lastrow
       
        If Range("V" & i).Value > 0 Then Range("Z" & i).Value = Range("V" & i).Value ' copy V to Z if V > 0
       
        Next i
               
        End If
               
       
    Application.EnableEvents = True 'Turn on events again
    End Sub

[Captain Sensible]

Actually thinking about it you'll probably need to remove , or comment out the

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub

Because you're using Application.Intersect that block of data V5:V20 doesn't get updated in the data A:P that's sent in those 16 columns.

[G78]

Brilliant, thanks for that Captain Sensible, you're the man! I'll give it a go tomorrow and see how I get on. Thanks

[G78]

Actually thinking about it you'll probably need to remove , or comment out the

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub

Because you're using Application.Intersect that block of data V5:V20 doesn't get updated in the data A:P that's sent in those 16 columns.

I did need to remove that section, but it's perfect thanks. It was great to have some working code and be able to tweak it and see how it works.

Thanks again for your help.