Spacing out a batch Find/Replace so that it proceeds in smaller 'chunks'

[d0rian]

The server I'm pulling stock quotes from has added a request-limit of 50 quote-requests / second; if you request more than that, it disconnects you. My current code requests 500 at once, so I need to revise it so that it spaces out those requests over 10 seconds.

The way the formulas are 'activated' in the code below is by replacing the '$' at the start of the string with a '=' so that Excel converts it to a formula (which sends the quote-request). Each row (from 43 to 452) has the formulas for one stock quote. As you can see below, I currently just do a batch Find/Replace for all 500 rows (D43:S452) at once, but I need it to do it in batches of 50 (e.g. D43:S92), then pause 1.25 seconds, then do it for the next batch (D93:S142), and so on.

Sub     
    Application.ScreenUpdating = False
    With Sheets("Kitty")
        With .Range("D43:S452")
                    .Replace What:="#", Replacement:="=", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
        End With
    End With
    Application.ScreenUpdating = True
    Application.CutCopyMode = False


End Sub

 

[shg]

You could use Application.OnTime to schedule a batch at 2 second intervals. As for Wait, the precision is 1 second, and if the server is anal, it may boot you if you use 1-second intervals.

 

[d0rian]

You could use Application.OnTime to schedule a batch at 2 second intervals. As for Wait, the precision is 1 second, and if the server is anal, it may boot you if you use 1-second intervals.

OK...is the "Application.OnTime" function different from Wait/Loop, in that it'll avoid the problem I posted where Excel is for some reason "holding" all of the RTD messages until the entirety of the code is finished running, and then just releasing them in 1 big batch?

 

[shg]

Try it.