Need vba to allow sheet formula to update before continuing

[Pat_43]

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.

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

 

[Peter_SSs]

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.