!! Challenge !!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: !! Challenge !!

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Phil
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Phil
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using DoEvents. Something like:

    Code:
    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Phil
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com