0

A macro I am trying to build essentially tells excel to:

  1. Select an item from a drop-down, which changes the value of another cell
  2. Then copy and paste that value onto another sheet
  3. Move down to the next item on the drop-down
  4. Repeat

I used the below code which worked. However, I am also using Bloomberg to pull data when I select a new item on the drop-down. It takes a few seconds to pull the data. Right now the code doesn't "wait" for Bloomberg to pull the data before pasting the value. Therefore, my question is, how do I incorporate a code that tells excel to essentially wait 20 seconds or so every time a new item is selected from the drop-down before commencing the next step.

Sub DebtAutopull()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Dim i As Long

Set dvCell = Worksheets("PULLER").Range("B2")
Set inputRange = Evaluate(dvCell.Validation.Formula1)

i = 1
Application.ScreenUpdating = False
For Each c In inputRange
    dvCell = c.Value

    Worksheets("PASTE").Cells(i, "E").Value = Worksheets("PULLER").Range("D2").Value
    i = i + 1

Next c
Application.ScreenUpdating = True

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
d_h
  • 1
  • https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-wait-method-excel – braX Jan 18 '18 at 16:46
  • A google search of "Excel VBA wait" returns the answer both on microsoft and the duplicate on stackoverflow. https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba – Karl Kristjansson Jan 18 '18 at 16:51
  • Possible duplicate of [VBA to refresh Bloomberg data not running in proper order](https://stackoverflow.com/questions/33661436/vba-to-refresh-bloomberg-data-not-running-in-proper-order) – assylias Jan 19 '18 at 08:58

1 Answers1

1

Add Application.Wait (Now + TimeValue("0:00:20")) where you need the pause.

dadler
  • 145
  • 12