Setting VBA using Time before a race starts.

[Sussexguy44]

Hi,

Im looking to get some cells copied to Values so I can see the % change in Traded volume. At 10 mins before the race I would like the traded volume percentage cells copied over as a value. I have set this up but it does not work as yet. It just gives me an error 13 debug. Any help would be welcome.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("D2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Target.Value = "00:10:00" Then
' MsgBox "Cell " & Target.Address & " has changed to " & Target.Value & "."
Range("Z5:Z18").Select
Selection.Copy
Range("AA5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
End Sub

[Captain Sensible]

Excel treats time as a number it just gets formatted as a time value in the sheet. So you'd use TimeValue("00:10:00") in VBA to represent the time.

Also you have to take into account that once the time goes negative BA sends the value in Cell D2 as text. Plus depending on your refresh rate, lags etc you might not catch 00:10:00 exactly so better to try to catch a range IE
00:10:10 to 00:10:00

So

If Target.Value <= timevalue("00:10:10") and Target.Value >= timevalue("00:10:00")

Not on my pc at the moment so can't check but hopefully you get the idea.

[Sussexguy44]

Thank you, Much appreciated

[Sussexguy44]

I still get the debug 13 message. I am sure I am nearly there, but not sure of the next step.

[Captain Sensible]

I'll be on my pc later and post up some code as I'm sure I've some timed sample on there

[Captain Sensible]

I'd guess it's because D2 switches to text after the off that's give the error

[Captain Sensible]

Here's some code I think I may have posted before that should hopefully work, you might need to tweek it to suit your needs or take parts out.

Basically it checks the market and if it's a new one it'll clear the data in AA so you might want to take that out if it's not needed.

Then it simply checks we haven't already run the code grabbing column Z, if we haven't it checks the time and if it's lesat than or equal to 10 minutes it timestamps the data. I it's not set it to exactly 10 minutes because there are always problems with internet lag etc But if you need exactly 10 minutes or nothing just change it or even set it to a timeslot of 00:10:00 to 00:09:55 or whatever you need. If there's bits in the code you don't understand just ask.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Static MyMarket As String
Dim lastRow As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Target.Parent

lastRow = .Range("Z" & Rows.Count).End(xlUp).Row

If .Range("A1").Value <> MyMarket Then
    MyMarket = .Range("A1").Value
    .Range("AA5:AA" & lastRow).Value = ""
End If


If WorksheetFunction.IsText(.Range("D2").Value) = False Or .Range("AA5").Value <> "" Then
    If .Range("D2").Value <= TimeValue("00:10:00") Then
               .Range("AA5:AA" & lastRow).Value = .Range("Z5:Z" & lastRow).Value
    End If
End If


End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

[Captain Sensible]

Just had a quick chance to check if it works and for some unknown reason AA5 can alternate between Text and Empty, no idea why could be the way BA is sending data or just my code. Either way I tweaked to below

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Columns.Count <> 16 Then Exit Sub
    Static MyMarket As String
    Dim lastRow As Long
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Target.Parent

    lastRow = .Range("Z" & Rows.Count).End(xlUp).Row

    If .Range("A1").Value <> MyMarket Then
        MyMarket = .Range("A1").Value
        .Range("AA5:AA" & lastRow).ClearContents
    End If


    If WorksheetFunction.IsText(.Range("D2").Value) = False And (IsEmpty(Range("AA5").Value) Or WorksheetFunction.IsText(.Range("AA5").Value)) Then
        If .Range("D2").Value <= TimeValue("00:10:00") Then
            .Range("AA5:AA" & lastRow).Value = .Range("A5:A" & lastRow).Value
        End If
    End If


    End With


    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True


    End Sub

[Sussexguy44]

Hiya,

Sincere thanks again. Firstly the code does work at the trigger point of the 10 min time request. Cheers for this. At present the column AA is not copying the adjacent cell Z but copying the horses name from column A. I have had a look however I cant see the solution. I am sure it is one small change but I dont know where it is. Any help would be welcome.

[Sussexguy44]

At this stage Im looking for the value in column Z to be copied over as a value to the adjacent column AA. Ill keep looking.

[Captain Sensible]

Just change the line in the code

from

Code: Select all

 .Range("AA5:AA" & lastRow).Value = .Range("A5:A" & lastRow).Value


to

Code: Select all

 .Range("AA5:AA" & lastRow).Value = .Range("Z5:Z" & lastRow).Value

[Sussexguy44]

Sincere thanks, this is all going well and I am semi automated. I have set up some triggers but I do need to use the time box which is more complex.
I am looking to place a trigger back bet if the time (D2) is 0:00:00 and any cell in the range AB5 to AB20 is 5 or greater. Do I need VBA or can this be done with a formula. Its the time box that is difficult.

[Captain Sensible]

Like I mentioned before excel treats time as a value but it gets displayed on the excel sheet in time format. You can simply change the format of D2 from time to number using format cells to see the actual values.

If you want to use 00:02:00 format in formulas or VBA you simply use the function TIMEVALUE.

So if you want to bet at exactly 00:00:00 AND only 00:00:00 just use TIMEVALUE("00:00:00") i..e

Code: Select all

=IF(AND($D$2=TIMEVALUE("00:00:00") ,AB5>=5),"BET","DON'T BET")

VBA

Code: Select all

If .Range("D2").Value = TimeValue("00:00:00")  And .Range("AB5").Value >= 5 Then ..............


[Sussexguy44]

Thanks again, I will be joining Gruss tomorrow on the back of your continued help.

=IF(AND($D$2<=TIMEVALUE("00:00:30"),AB5>=4),"1","0") This I have put in place hoping that it offers a range of time from below 30 seconds before the off. I have found that the specific time does not work (as you mentioned ) due to refresh rate.

Trigger column is = IF(AND(AE5=1,$E$2="Not In Play"),"BACK","") The second one I have (again from your previous advice) takes the 1 from the selected column (AE5) and should place a back bet if not in play.

This should give the system the time between 30 second before the off and the actual off time to place a bet.

Does this look OK?

[Sussexguy44]

Re Previous, the trigger formula does not work just now.