VBA code help please

[danjuma]

Say if cell F5 = 10.0, and I want cell AA1 to have a particular value (let's say 1 for this example) only if F5 has been equal to 10.0 for a period of say at least 5 seconds (basically trying to avoid odd spikes in the market), I presume I would need a VBA code for this. Could somebody assist with a code for this please?

Many thanks
Dan

[Ian]

Could you simply count the number of refreshes that F5 has remained constant ?

[danjuma]

How would you do this Ian? Thanks

[Ian]

Something like this .........

Code: Select all

Dim prevOdds As Long
Dim refreshCount As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

    With Sheets("Current Race")
   
       If Target.Columns.Count = 16 Then
   
               
                If .Cells(5, 6).Value = prevOdds Then
               
                    refreshCount = refreshCount + 1
                   
                Else
                                   
                    refreshCount = 0
               
                End If
               
                prevOdds = .Cells(5, 6).Value
               
                If refreshCount >= 5 Then
               
                    .Cells(1, 27).Value = "Y"
                   
                Else
               
                    .Cells(1, 27).Value = ""
                                   
                End If
     
       End If

    End With

End Sub


[danjuma]

Something like this .........

Code: Select all

Dim prevOdds As Long
Dim refreshCount As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

    With Sheets("Current Race")
   
       If Target.Columns.Count = 16 Then
   
               
                If .Cells(5, 6).Value = prevOdds Then
               
                    refreshCount = refreshCount + 1
                   
                Else
                                   
                    refreshCount = 0
               
                End If
               
                prevOdds = .Cells(5, 6).Value
               
                If refreshCount >= 5 Then
               
                    .Cells(1, 27).Value = "Y"
                   
                Else
               
                    .Cells(1, 27).Value = ""
                                   
                End If
     
       End If

    End With

End Sub


Sir, you are a gent, many thanks!!!

Just one question, with the statement: With Sheets("Current Race"), is "Current Race" referring to the name of the sheet or something and do I need to change it to the name of my sheet. Sorry, if it's a stupid question, am not well versed in VBA. Cheers.

[Ian]

Yes "Current Race" is the name of the sheet so you would need to change it to whatever sheet you are logging to.

[danjuma]

Ian,

Just one more thing that came to mind. It looks like you code is based on the assumption that BA is refreshing every second, what if it was refreshing every 0.3 seconds? Should I just change the statement: If refreshCount >= 5 Then to something like If refreshCount >= 15 Then ?

Cheers
Dan

[danjuma]

Yes "Current Race" is the name of the sheet so you would need to change it to whatever sheet you are logging to.

Thanks.

[Ian]

Ian,

Just one more thing that came to mind. It looks like you code is based on the assumption that BA is refreshing every second, what if it was refreshing every 0.3 seconds? Should I just change the statement: If refreshCount >= 5 Then to something like If refreshCount >= 15 Then ?

Cheers
Dan

Exactly.

[danjuma]

Ian,

Just one more thing that came to mind. It looks like you code is based on the assumption that BA is refreshing every second, what if it was refreshing every 0.3 seconds? Should I just change the statement: If refreshCount >= 5 Then to something like If refreshCount >= 15 Then ?

Cheers
Dan

Exactly.

Many thanks.