Refresh rate value

[Daywalker]

hi

my sheet starts at A10. i want refresh rate to be 1 second upto 10 mins before off then 0.2 seconds until 30 seconds before off (thats when my triggers stop going in) then at 10 seconds to off refresh rate = -1, selecting next race from quick pick list. cell U6 is seconds to off. will this work?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

If Range("U6") > 600 Then
Range("Q11") = 1
ElseIf Range("U6") > 30 Then
Range("Q11") = 0.2
ElseIf Range("U6") > 10 Then
Range("Q11") = -1
End If

Application.EnableEvents = True
End Sub

thanks

[osknows]

Hello Daywalker,


I think this will work

Code: Select all

Dim nextracetrigger As Integer
Const inplayrefresh As Double = 0.2
Const normalrefresh As Double = 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

With ThisWorkbook.Sheets(Target.Worksheet.Name)
If .Range("U6") > 30 Then
.Range("Q11") = normalrefresh
ElseIf .Range("U6") <= 30 and .Range("U6") >10  Then
.Range("Q11") = inplayrefresh
ElseIf .Range("U6")<= 10 And nextracetrigger = 0 Then
lastrace = .Range("A10").Value
.Range("Q11").Value = -1
nextracetrigger = 1
End If

If lastrace <> .Range("A10").Value Then
nextracetrigger = 0
End If

End With

Application.EnableEvents = True
End Sub


[Daywalker]

Thanks osknows, i'll have a dabble

[Daywalker]

hi osknows

tried it but when it jumps to next race it leaves -1 in the refresh rate and jumps to next race after that, then it changes to 1 or 0.2. any ideas?

[Daywalker]

now i've fannied with it and it works but i dont know what i've done. i'll keep an eye on it

[Daywalker]

nope. sometimes it jumps one race sometimes two.

[GaryRussell]

osknows' code looks ok to me. How are you populating cell U6, with a formula or with VBA?

[osknows]

It would also help if I included 'Dim lastrace As String'

The code should be

Code: Select all

Dim nextracetrigger As Integer
Dim lastrace As String
Const inplayrefresh As Double = 0.2
Const normalrefresh As Double = 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

With ThisWorkbook.Sheets(Target.Worksheet.Name)
If .Range("U6") > 30 Then
.Range("Q11") = normalrefresh
ElseIf .Range("U6") <= 30 and .Range("U6") >10  Then
.Range("Q11") = inplayrefresh
ElseIf .Range("U6")<= 10 And nextracetrigger = 0 Then
lastrace = .Range("A10").Value
.Range("Q11").Value = -1
nextracetrigger = 1
End If

If lastrace <> .Range("A10").Value Then
nextracetrigger = 0
End If

End With

Application.EnableEvents = True
End Sub



This has been tested to work ok with Gary's famous formula in U6

cell U6 =IF(LEFT(D2)<>"-",(HOUR(D2)*3600)+(MINUTE(D2)*60)+SECOND(D2),-((HOUR(SUBSTITUTE(D2,"-",""))*3600)+(MINUTE(SUBSTITUTE(D2,"-",""))*60)+SECOND(SUBSTITUTE(D2,"-",""))))

you might have to swap cell D2 for D11 in your case

[Daywalker]

Gary, i'm using the formula that's in osknows last post to populate U6, i found it in another post on the forum.

Osknows, i've tried your new code and it works perfect. before the '-1' seemed to flash and hang around until the next market populated excell which took a few seconds, in which time the highlighter in the quick pick list jumped again. Thanks very much for this, it's ace

Today was my first day using my excel sheet through Gruss. the sheet entered bets in 20 markets, placing 55 bets with tick offset and stop loss. i used £2 stakes and only lost 25 pence. i think this is well cool and down to the knowledge available on this forum and the fantastic gruss product

Thanks again