Alternative methods for coverting formulas to values in Excel using VBA -

I first want to make a process to pull data from Bloomberg and want to change all the sources based on their values. So far, I have come up with two approaches 1. Use BDH code to draw data first for the range of cells, and then paste all the data as values ​​after populated. 2. Use the BDH code to drag the data and paste the code as a value immediately before executing the next cell. The following is my code (I use another approach):

j = last - time = 6 for time active =. For kel (j, 1) i = 3, Colonel C ticker = ActiveSheat.Cell (2, I). Value ActiveSite Seals (J, I). Formula = "= BDH (" "" and "" ticker and "", "" last "", "" "" & Amp;; & amp; time and; "", "" and time and " "" Day = a "," "fill = p" ")" application Waiting (Now + Time Vila ("00:00: 03")) ActiveSheet.Cells (j, i) .Value = ActiveSheet.Cells (j, i) .Value Next Next

When I run this code on Bloomberg, paste the chip as T and they are still the code I think 3 seconds in the application. The wait is not enough, but I want to change my whole method and do not want to count how many seconds one price has to be populated. Does anyone have experience with this and can provide a fast way to populate prices from Bloomberg? Although the first approach I mentioned above is also an option, but I like to get the values ​​while popularizing the data. Thanks!

I actually came up with an answer to my problem .. but it still uses onetime There are other ways to make sure that

  for sub pulldata () = Jammu = last - for 6 time = ActiveSheet.Cells (j, 1) for i = 3 Lastcol 'starts with the Colonel C ticker = ActiveSheet.Cells (2, i) .Value ActiveSheet.Cells (j, i). Formula = "= BDH (" "" & "", "" Last_Price "", "" "and time and" "" "" and time and "", "" day = one "," P "") "Start a new sub time to eliminate the last macro all pestespicious () from time to time + timevilla (" 00: 00:03 ")," pastestyle "next next end sub. Intecol finalcol = ActiveSheet.Cells (1, ActiveSheet.Columns.Count) .end (xlToLeft) .color set myrange = category (ActiveSheet.Cells (final - 6, 3), ActiveSheet.Cells (last, lastcol)). My Category Values ​​= Diamonds Value End Sub