Need delay before new sub executes

[artz]

Hi,

I have a sub in a workbook that performs a web query and places data into columns on a worksheet. (stock market data)

Some of the data are subsequently used to perform calculations in other columns on the same worksheet.

Before End Sub in the web query sub, I call another sub to place some of the downloaded data from the web query and some of the calculations performed on this data into a different worksheet in the same workbook.

The problem is that the calculations do not finish before the call to the new sub is done causing missing data on the new worksheet , resulting in cells with N/A or DIV/0, etc.

I tried to insert a delay into the first sub and it did just that; simply paused for the old sub for the amount of time specified, it did not just delay the call to the new sub.

Seems that there must be a way to say "if worksheet calculations complete, then call New sub()" from the old sub. (old sub is initiated by a CommandButton click) I want the new sub to also be initiated by the same CommandButton click, but after the calculations initiated by old sub are complete. Make sense?

Does anyone in the Forum have a suggestion on how to do this?

Thanks,

Art

 

[CWatts]

Have you tried:

Application.Wait (Now + TimeValue("00:00:30"))

 

[artz]

Thanks for your suggestion that is what I tried. The whole sub kind of hangs, then the new sub runs. It wasn't the way I wanted the subs to run to run execution-wise. -Art

 

[VoG]

Try

Do Until Application.CalculationState = xlDone
    DoEvents
Loop

 

[artz]

Try

Do Until Application.CalculationState = xlDone
    DoEvents
Loop

Thanks for your suggestion, I am not sure how to put this into my code.

 

[CWatts]

PUt it right before you call the other Sub