logging a price at 2 different time intervals

[nhmaster]

does anyone know what formula I would use to to record a price 15 mins before the off and another 2 minutes before the off

cheers Shaun

[phrenetic]

I think you need to rely on VBA to achieve what you want.

Alistair

[nhmaster]

Hi Alistar

Must admit not my strong point OK with excel not done much VBA can you throw any light?? Oh how are you getting on with the Safebet plan I sent??

Shaun

[Captain Sensible]

Heres some code I used once to log the odds prices at various intervals


Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Target.Rows.Count
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(2, 4) > Cells(4, 35) Then
If Cells(2, 4) < Cells(3, 35) Then
For i = 5 To 35
Cells(i, 30) = Cells(i, 6)
Next i
Else
Cells(1, 31) = "Done 10"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(2, 4) > Cells(6, 35) Then
If Cells(2, 4) < Cells(5, 35) Then
For i = 5 To 35
Cells(i, 31) = Cells(i, 6)
Next i
Else
Cells(1, 32) = "Waiting to log"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


End Sub


Basically you just press Alt + F11 ad paste it into the excel visual basic page.

I'd had it set to have a first logging start time and end time in AI3 and AI4
and the second logging start times in AI5 & AI6

The prices from the available to back column F then get pasted into AD first log and AE respectively. Just play around with it to change the data you want to log i.e. last price matched or lay columns and the columns you want it pasted into remembering Cells(2, 4) refers to D2 i.e first number is row number and second number column letter so Cells(6,35) is AI6 etc

[Shaun]

Glad i did a search of this forum, this is the code i have been trying to work out for days, nobody on other forums seam to understand exactly what i need.

Thanks Shaun

[drewrell]

Heres some code I used once to log the odds prices at various intervals


Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Target.Rows.Count
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(2, 4) > Cells(4, 35) Then
If Cells(2, 4) < Cells(3, 35) Then
For i = 5 To 35
Cells(i, 30) = Cells(i, 6)
Next i
Else
Cells(1, 31) = "Done 10"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(2, 4) > Cells(6, 35) Then
If Cells(2, 4) < Cells(5, 35) Then
For i = 5 To 35
Cells(i, 31) = Cells(i, 6)
Next i
Else
Cells(1, 32) = "Waiting to log"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


End Sub


Basically you just press Alt + F11 ad paste it into the excel visual basic page.

I'd had it set to have a first logging start time and end time in AI3 and AI4
and the second logging start times in AI5 & AI6

The prices from the available to back column F then get pasted into AD first log and AE respectively. Just play around with it to change the data you want to log i.e. last price matched or lay columns and the columns you want it pasted into remembering Cells(2, 4) refers to D2 i.e first number is row number and second number column letter so Cells(6,35) is AI6 etc

Have just started using this and works great but how do I clear the data from the excel sheet when the next race loads?
I have them to auto load but the recorded data from the earlier race is still there.

Thanks

[Captain Sensible]

Try this code, hopefully it'll work as I haven't tested it

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("AD5:AE50").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub