!! Challenge !!

[philn]

Help please,
I have a macro whose purpose is to trigger a number of other macros.

The problem:
is that the MAIN macro continues to the next step while not allowing the previous step to complete all cell calcs.
So I end up with alot of #N/A instead of the actual value $1.98 once the cell calc have completed.

The question:
How do I keep a macro from contiuing before all cell calcs have completed in the previous step?

Here is the code:
it fails between macros "Update_Day_Arrays" and "Move_to_History.
I have tried to put in a pause, but it just loops and still does not allow for cell calc completion.

GETNEXT:
Range("K65").Select
Selection.Copy
Range("K64").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Run "'Auto Buy and Sell.xls'!Update_DAY_Arrays"
Application.Run "'Auto Buy and Sell.xls'!Move_to_History"
WAIT:
If Range("K64") = 0 Then
GoTo KILL
ElseIf Range("j33") <> Range("j70") Then
GoTo WAIT
Else
GoTo GETNEXT
End If
KILL:


[ This Message was edited by: philn on 2002-02-24 11:22 ]

[ This Message was edited by: philn on 2002-02-24 11:23 ]

[ This Message was edited by: philn on 2002-02-24 11:31 ]

[ This Message was edited by: philn on 2002-02-24 12:11 ]

[ This Message was edited by: philn on 2002-02-24 12:32 ]

[Paul B]

how about using this to make the marco wait, set it to how long you need. Application.Wait Now + TimeValue("00:00:02")

[philn]

OK, that cleans up my code and it does wait. But it seems that all cell calculations are suspended while the macro is running.
So, after waiting the macro continues, I still end up with the same results "#N/A!". Afterwhich, the macro ends and the cell calcs start, but by then its to late.


Any other good ideas? I would greatly appriciate any feedback.
Thanks,
Phil

[Russell Hauf]

Try using DoEvents. Something like:

Option Explicit

Sub rhWait()
    Dim dblTime As Double
    
    dblTime = Now() + TimeValue("00:00:02")
    
    Do While Now() < dblTime
        DoEvents
    Loop

End Sub

Hope this helps,

Russell

[philn]

Thank you Russel, Thank you, Thank you,
This worked GREAT!!! I was truely ready to give up.

Here is the final code:

Option Explicit
Dim dblTime As Double
Sub Get_Next_Stock()
'
'
GETNEXT:
Range("K65").Select
Selection.Copy
Range("K64").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
If Range("K64") = 0 Then
GoTo KILL
ElseIf Range("j33") = Range("j70") Then
Application.Run "'Auto Buy and Sell.xls'!Update_DAY_Arrays"
dblTime = Now() + TimeValue("00:00:30")
Do While Now() < dblTime
DoEvents
Loop
Application.Run "'Auto Buy and Sell.xls'!Move_to_History"
GoTo GETNEXT
End If
KILL:
End Sub


[ This Message was edited by: philn on 2002-02-24 21:32 ]