View Full Version : Make excel pause

01-14-2008, 03:41 PM
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
01-14-2008, 07:55 PM
its done like this:

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

01-14-2008, 11:21 PM
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...... ??

01-15-2008, 03:59 AM
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

01-15-2008, 07:12 PM
'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)

MsgBox "Done"

End Sub