How to pause and wait for data to populate during code

[auto.pilot]

Using XL2007, I have the code below, which simply copies a range of data which has been populated from a data feed. This process is repeated based on line numbers found in column A (several hundred times). It all works, except that the code seems to disable the data feed. No matter how much time I use in the WaitNow line, the data feed does not populate the excel spreadsheet. As such, each of my 300+ results are populated with data from the first line only.

Would appreciate any and all advice to pause the macro or allow the data feed to populate while the macro is running.

Thanks in advance.

jim

Sub CashFlow()


 Application.ScreenUpdating = False
 Dim Rng As Range, Dn As Range
 ' Section below sets the range line numbers
 Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 'section below gets the values calculated in for each scenario, then pastes the results of each

    For Each Dn In Rng
         Range("D2") = Dn
         Application.Wait Now() + TimeValue("00:00:10")
         Range("C1:I370").Copy
         Range("CCC1").Select
         Selection.End(xlToLeft).Offset(0, 1).Select
         Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Next Dn
 Application.CutCopyMode = False

 Range("D2").Value = 1
 Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[Gary McMaster]

An easy thing to try would be to replace:

Application.Wait Now() + TimeValue("00:00:10")

With:

DoEvents

That will allow the operating system to do its thing on every iteration.

Gary

 

[amerifax]

Gary I also can use this time delay. But I was wondering if there is anyway for Excel to make sure there is a value in the effected cell before continuing.

 

[Gary McMaster]

I don't know what you mean. Can you show some code and give a better explanation?

Gary

 

[auto.pilot]

Could use some additional assistance, seeking to pause the code and wait for fields to fill from a database before proceeding with copy/paste special values.

I have tried:

 Application.Wait Now() + TimeValue("00:00:10")

and

 DoEvents

Next, in cell D10, I added a flag which is populated with the word 'Complete' only after the data base fields are populated as shown below:

  Sub CashFlow()

 Dim Rng As Range, Dn As Range, Flg As Range
 Dim wb As Workbook, ws As Worksheet
 ' section below sets the Flag Range in cell D10
 Set Flg = Range("D10")
 ' Section below sets the range line numbers
 Range("D2").Value = 1
 Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 'section below gets the values calculated in for each scenario, then pastes the results of each

    For Each Dn In Rng
    'Application.Wait Now() + TimeValue("00:00:10")  < line does not allow fields to populate
    'DoEvents   < line also does not allow fields to populate
    If Flg.Value = "Complete" Then
        Range("D2") = Dn
        Range("C1:I370").Copy
        Range("CCC1").Select
        Selection.End(xlToLeft).Offset(0, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End If
    Next Dn

 Application.CutCopyMode = False

 Range("D2").Value = 1
 Range("A1").Select
' Application.ScreenUpdating = True
 End Sub

Using this code, cell D10 never changes to 'Complete' because the macro appears to freeze the data feed. Would appreciate any other advice.

Thanks
jim