Wait until in a Loop

Question

Hi everyone,

I have a problem with a For Loop. What I have to do is this:

1. Copy the first cell of ColumnA in F2 (in the yellow highlighted cell).
2. Then all the data have to refresh.
3. Then the cell F3 is update.
4. The cell F3 is paste in B1 (the relative cell on the right of the columnA cell copy in the first step).

Here is the code:

For x = 1 To 5

    Cells(x, 1).Select  
    Selection.Copy  
    Range("F2").Select  
    ActiveSheet.Paste  

HERE THERE MUST BE A FUNCTION THAT PERMIT TO WAIT UNTIL THE REFRESH

    Range("F3").Select  
    Selection.Copy  
    Cells(x, 2).Select  
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  
        :=False, Transpose:=True  

Next

So the problem is that the loop copy and paste "#VALUE!" and doesn't wait the calculation.

I hope someone can help me.

Davide

Answer 1

Hello,

Welcome to Microsoft Q&A!

Currently, Microsoft Q&A supports the products listed over here: supported topics (more to be added later on).

Your question about using VBA is not supported yet now. You could ask about this in Excel for Developers.

Thank you.