Results 1 to 2 of 2

Thread: Need vba to allow sheet formula to update before continuing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need vba to allow sheet formula to update before continuing

    Iíve got a loop that puts an ID number into a cell and thencopies the results from another cell to an output sheet based on formula on thesheet, however the vba code doesnít wait for the formula to update the results cells and so copies whatever was in the result cells at the start of the macrorunning.

    My question is this Ė can I get the code to force the sheetformula to run and update the results cells before it copies the results to theoutput sheet? The calculate and wait options shown in the code below haveníthad the desired effect so far.

    Code:
        For Each DefectIDsIn OutRng
        Debug.PrintDefectIDs.Value
           Sheets("FAC Assigning tool").Range("C2").Value =DefectIDs
            Calculate
           Application.Wait (Now + TimeValue("00:00:01"))
            CallFindAllFrequencies 'Checks defect ID against FACs and copies result to blankrows on Output sheet
        Next DefectIDs

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,966
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Need vba to allow sheet formula to update before continuing

    Welcome to the MrExcel board!

    Not sure if it will help but you could try the Statement DoEvents in place of your other two attempts.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •