VBA help gathering odds before the off

[Captain Sensible]

I'm pretty clueless with vba and rarely use it but there seem to be some experienced vba users out here.

Could anyone point me in the direction of writing code to grab the last last lay odds in column H just before the off. I'd imagine the script would need to copy the column on each refresh and then keep them static once E2 shows as suspended/In Play. Not sure how bt assistant updates the info just before and after the off so it may need the script to capture the last two refreshes just in case it doesn't catch the suspended flip.

Not looking for someone to write the exact code just a rough idea would great as I always enjoy a challenge

Thanks

[Mitch]

Ok, roughly then.......

On each refresh, copy and paste the data you want to another place that doesn't get overwritten.
Before the copy and paste code see if the market is suspended because often the prices get zeroed before the market turns in-play.
If it isn't then carry on with the copy and paste.
If it is then don't copy and paste, instead set another cell to "DONE" or something similar that you can check as well as the suspended so that when the suspended disappears the code still won't overwrite the info.
The only drawback to that is if the market becomes suspended due to a withdrawal but for my purposes the odd occasion that happens doesn't bother me.

I've already written it for a sheet of mine so I'd be happy to help if you need it.

Do you just need to see the frozen prices at the off, or do you want to record them to another sheet race after race to look at later?

[Captain Sensible]

Thanks Mitch ,

I was thinking along the lines of copying the last matched column on each refresh whilst E2=not in play and F2 is not equal to suspended. I tried to amend some of the code you'd posted on here before but no joy.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

For i = 5 To 10
If Cells(i, 20) = "PENDING" Then Cells(i, 26) = Cells(i, 18)
Next i

Application.EnableEvents = True

End Sub

And just used an if statement to input the "PENDING" when the criteria was met but it didn't work. Maybe I haven't set the range ?

Also considered amending some other code you posted

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [Z1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [Z1].Value
Range("X5:X50").Value = Range("O5:O50").Value""

End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

But again no joy with having Z1 change nothing happens - When writing vba do I need to close it down and restart to effectively kick off the VBA

No need for them to be stored as they'd be used to kick off some in running bets so I need them to be as accurate as possible and considered the last matched column may give that info. My formulas would do a couple of checks before submitting just to be on the safe side

[Ian]

Isn't one of the problems that BA writes the data a line at a time to Excel ? So the worksheet change event can be run a number of times on each refresh.

[Mitch]

Isn't one of the problems that BA writes the data a line at a time to Excel ? So the worksheet change event can be run a number of times on each refresh.

You're right for the bet details, but the main info about selections and prices etc is done in one go.
The line...
If Target.Columns.Count <> 16 Then Exit Sub
...makes sure that the code is only run once for each refresh as the main info is 16 columns wide.

Captain.. Do you only want to copy prices for selections that you have bet on, or every selection regardless?
Do you only need them there until the next race loads or do you want to save them as well?

[Captain Sensible]

Captain.. Do you only want to copy prices for selections that you have bet on, or every selection regardless?
Do you only need them there until the next race loads or do you want to save them as well?

Every selection is needed - no need to save them for the next race , I was considering something simple like
Range("H4:H36").Select
Selection.Copy
Range("AA4").Select
ActiveSheet.Paste

Just need a way of kicking it off. I think the Private Sub Worksheet_Calculate() won't work as I'd be using a formula to kick off the change in the cell rather than a physical change in the cell.

BA is being a complete pain with excel at the moment and doesn't update for me. No idea why but that my be stopping some of my efforts working. It'd be a good idea if gary restored some previous stable versions on the site especially with him going live shortly

[Mitch]

I don't think the second code snippet in your original post is mine as I can't get Worksheet_Calculate to work either so I always use Worksheet_Change.

In your example above, you don't need to select things before you copy or paste, although that is how the macro recorder writes it for you (just one of those things you learn the more you use vba).

Your code could be...

Code: Select all

Range("H4:H36").Copy Destination:=Range("AA4")

If it were me (probably because I'm an anorak!) I'd include a variable to only copy the right amount of cells.

When you use the Worksheet_Change routine you get passed a range called Target, which is the range on the sheet that has been changed.

I would use a variable called lastrow (for example) and give it the value Target.Rows.Count which is how many rows have changed.

Code: Select all

lastrow = Target.Rows.Count

Now you can use lastrow in your range to only get the cells you want.

Code: Select all

Range("H4:H" & lastrow).Copy Destination:=Range("AA4")

Sorry if I've gone over the top for such a simple thing, but these are the sort of things I wish someone had told me when I started messing around with vba.

[Captain Sensible]

Thanks mitch I had a bit of success with



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
Range("H4:H50").Copy Destination:=Range("AA4")
End If
End Sub

Sadly BA has gone on the blink with excel logging just as I'm trying to check things - Can i set the range to act if E" = Not In Play and F2="" ? by amending 'If Not Intersect(Target, Range("E2")) Is Nothing Then' somehow. At the moment it just copies everytime regardless of if the cell has changed value as I suppose each refresh kicks it off. That'd mean it'll also kick off when it goes in play and just log the in play odds

I'm afraid I have no clue what the code is dong as it's just copied from websites

I took out the lastrow = Target.Rows.Count for now as BA is being a pain t

[KevinTHFC]

As a quick and dirty something like the following works. Tested it on the dogs and seemed OK


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(2, 5) = "Not In Play" Then
If Cells(2, 6) <> "Suspended" Then
For i = 5 To 10
Cells(i + 20, = Cells(i,
Next i
Else
Cells(41, = "Done"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If

End Sub

[KevinTHFC]

Ooops this looks better

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(2, 5) = "Not In Play" Then
If Cells(2, 6) <> "Suspended" Then
For i = 5 To 10
Cells(i + 20,8) = Cells(i,8) 
Next i
Else
Cells(41,8)  = "Done"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If

End Sub

[Captain Sensible]

Thanks kevin seems to be working just need to check when the markets flip to suspended. Checking the dogs now

Had to amned the line

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

as I imagine thats set for the api version and I've got to use the non api at the mon=ment plus I had some other cell changing so I'll figure out that at a later time

Market just went into play and it worked fine - just need that winning strategy now I guess - thanks again Mitch and kevin

[Mitch]

Thanks mitch I had a bit of success with



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
Range("H4:H50").Copy Destination:=Range("AA4")
End If
End Sub

Sadly BA has gone on the blink with excel logging just as I'm trying to check things - Can i set the range to act if E" = Not In Play and F2="" ? by amending 'If Not Intersect(Target, Range("E2")) Is Nothing Then' somehow. At the moment it just copies everytime regardless of if the cell has changed value as I suppose each refresh kicks it off. That'd mean it'll also kick off when it goes in play and just log the in play odds

I'm afraid I have no clue what the code is dong as it's just copied from websites

I took out the lastrow = Target.Rows.Count for now as BA is being a pain t

The line with Intersect in it is doing the same job as my If Target.Columns.Count <> 16 line ie. only firing the code if the main data has been pasted, and not individual bet info for each selection.

You can do what you suggested with the In Play and Suspended as Kevin has done in the post above (Cells(2, 5) is the same as Range("E2")).

For what it's worth this would be my effort...

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Columns.Count <> 16 Then Exit Sub
    If Range("E2") = "In Play" Or Range("F2") = "Suspended" Then Exit Sub
    Application.EnableEvents = False
   
    lastrow = Target.Rows.Count
    Range("H2:H" & lastrow).Copy Destination:=Range("AA4")
   
    Application.EnableEvents = True
   
End Sub


Strictly speaking you don't need the Application.EnableEvents lines but I think it's good practice to put them in in case the code changes at a leter date where it would matter.

[Captain Sensible]

Cheers mitch I've managed to get everything working with a mixture of both codes


Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Target.Rows.Count
If Target.Columns.Count <> 13 Then Exit Sub 'non api version'
Application.EnableEvents = False
If Cells(2, 5) = "Not In Play" Then
If Cells(2, 6) <> "Suspended" Then
For i = 5 To lastrow
Cells(i, 28 ) = Cells(i, 8 )
Next i
Else
Cells(1, 26) = "Done"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If

End Sub

Seems to be working ok