Some Excel help required

[doris_day]

If certain conditions are present I want Excel to trigger a move to the next race and I've been using the code provided from Osknows viz:

Dim marketChanging As Boolean, currentMarket As String

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 Not marketChanging And .Range("THIS CELL").Value = "THIS VALUE" Then
marketChanging = True
currentMarket = .Range("A1").Value
.Range("Q2").Value = -1
Else
If .Range("A1").Value <> currentMarket Then marketChanging = False
.Range("Q2").Formula = "=ADD YOUR FORMULA"
End If
End With

Application.EnableEvents = True

End Sub

That seems to work fine although, because I'm getting data from another source too I need to wait for about 5 seconds after a new market has been loaded before starting the routine. I've tried a "WAIT" line of code viz Application.Wait (Now() + "00:00:05") where I thought appropriate but its not working and I'm not certain this is the correct routine required anyway.
Basically, I want to load the first race of the day, once loaded with Betfair wait 5 secs so other data source can load and calculations be made, then if CellX >= Y move forward to next race, Else keep checking CellX after each normal refresh etc

Any help appreciated.

[Captain Sensible]

Have you considered using a variable to count each refresh? I had something similar than waited for conditions to be met but also did a further critieria of 10 refreshes before moving on.

So you code would be amended a bit like

Dim counter as currency

If Not marketChanging And .Range("THIS CELL").Value = "THIS VALUE" Then
counter=counter+1


ElseIf Not marketChanging And .Range("THIS CELL").Value = "THIS VALUE" And counter >10 Then
counter=0
....... other code

[/i][/quote]

[Ian]

How are you getting your data from the other source ?

[doris_day]

Magic Captn. That did the trick...

Ian, I'm scraping Ladbrokes with some regex VBA Osknows wrote for me and which works well.

[Ian]

I was going to suggest that you have a flag which you turn off when going into the scraping routine and turn on when you exit it and then test the flag subsequently.

[doris_day]

Thanks for the suggestion Ian.