Pause loop to give excel time to calculate

[Carbuyer0002]

Hi all,

From reading through this site, I've written a macro to copy an input from a list, paste it into a model that calculates based on data pulled in from an external source, and then paste specials the results back to the list. However-- when I run it, the macro runs faster than excel can pull in the external data so the pasted results are all inaccurate. I think what it needs is a way to pause the macro to give excel time to catch up before continuing, and from reading other posts maybe something this is the ticket:

" Application.OnTime Now + TimeValue("00:00:10") "

But it's not working. I don't think I am nesting it the right way. Any help would be greatly appreciated! See Below:

Sub Macro3()
'
Dim i As Integer
For i = 1 To 100
Range("A" & i).Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Application.OnTime Now + TimeValue("00:00:10")
Range("G1:H1").Select
Application.CutCopyMode = False
Selection.Copy
Range("B" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A" & i + 1).Select
Application.CutCopyMode = False
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Application.OnTime Now + TimeValue("00:00:10")
Range("G1:H1").Select
Application.CutCopyMode = False
Selection.Copy
Range("B" & i + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next i

End Sub

 

[oldbrewer]

I use for delay=1 to 1000000000:next delay

if necessary put for delay2=1 to 50:next delay2 in front

then you can change the 50 up or down

 

[Carbuyer0002]

Thank you, "for delay..." makes the code pause like you said. However the pasted results are still incorrect. I must have an error somewhere else in the code. Any ideas?

 

[oldbrewer]

extract one piece of data manually from your source, compare how you did it with your code. I have no experience of pulling data from websites.