Make excel pause

[wakwak1]

I am fetching external data in cell E23 and it takes maybe half a second for it to fetch properly. I have another formula in G23 that refers to E23, and when I run my macro, because E23 is "N/A" for a brief second before pulling the external data, the macro screws up. I need to get excel to "pause" for a very short time to allow the external data to be fetched, and then go ahead and calculate G23 formula etc.

[Simon Lloyd]

its done like this:

Application.Wait Now + TimeValue("00:00:02")
change the time to suit!

[wakwak1]

hmm still doesn't work...i think because Application.Wait makes the entire sheet "stop" ..but I still need those "dynamic" cells to keep updating..... hmmm...... ??

[anandbohra]

you can call your macro with ontime event

like before running your code for web query u can set

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

where my_Procedure will the macro which u want to run after completing webquery.

this will not stop whole application rather makes your macro run after some time

[Paul_Hossler]

'nuther way

I forced an error in the cell (=1/0), started the macro, and then plugged a number into the cell and the macro did a graceful exit



Sub LoopAndWait()

MsgBox "Started waiting"

While IsError(ActiveSheet.Range("E3").Value)
DoEvents
Wend


MsgBox "Done"

End Sub


Paul