=BDH() Formula shows as #N/A until after the Sub has finished running

[j4ttlife]

I'm pulling in data from Bloomberg using '=BDH()' formula, however this shows as '#N/A Requesting Data...'. This formula is in the middle of the Subroutine. At the end I am trying to find the sum of values pulled in from Bloomberg using the =BDH formula.

My issue is that as the =BDH formula doesn't display the values until after the module has finished running, hence the =SUM function used at the very end shows a blank as no values are ready to be added within the time the macro runs.

I've tried a few solutions without luck such as 'Application.Wait', 'Application.ScreenUpdating' without luck.

Are there any ways I can refresh the =BDH values before the code advances to the =SUM part of my subroutine? A snip of the code in question is:

Sub exampleCode()


'BDH formula used to pull in midprice. This stays as #N/A until after the entire module has run.
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow
Dim currency1 As String
currency1 = Range("A" & i).Value
    
Dim lastDayOfPrevMonth As Date
lastDayOfPrevMonth = DateSerial(Year(Date), Month(Date), 0)
    
    If currency1 = "AUD" Then
        Range("D" & i).Formula = "=BDH(""AUDUSD BGN Curncy"",""PX_MID"",""" & Format(lastDayOfPrevMonth, "mm/dd/yyyy") & """)"
    ElseIf currency1 = "CAD" Then
        Range("D" & i).Formula = "=BDH(""CADUSD BGN Curncy"",""PX_MID"",""" & Format(lastDayOfPrevMonth, "mm/dd/yyyy") & """)"
    ElseIf currency1 = "CHF" Then
        Range("D" & i).Formula = "=BDH(""CHFUSD BGN Curncy"",""PX_MID"",""" & Format(lastDayOfPrevMonth, "mm/dd/yyyy") & """)"

End if

Next i



'Sums the values pulled in the above If statements
TRow = WorksheetFunction.Match("Total", Range("A:A"), 0)

Dim lastRow1 As Long
Dim ix As Long

    lastRow1 = Cells(Rows.Count, "D").End(xlUp).Row
    
    For ix = 6 To lastRow1
    If Not IsEmpty(Range("C" & ix)) And Not IsEmpty(Range("D" & ix)) Then
        Range("E" & ix).Value = Range("C" & ix).Value * Range("D" & ix).Value
    End If
    
    Next ix

If GTRow > 0 Then
    Cells(GTRow, "E").Value = WorksheetFunction.Sum(Range("E6:E" & lastRow1))
End If


End Sub

What adjustments can I make to ensure the above =BDH formula shows the true value/refreshes before moving onto the Sum function?

 

[Micron]

maybe try cycling calculations on and off?

VBA: Is it possible to turn off autocalculate for one sheet only?

Using VBA, is it possible to turn off the autocalculate for one sheet only? Sheet3 has a lot of calculating formulas, I just want to disable this one sheet so that the autocalculate works on the other sheets (the macro needs those sheets to autocalculate). If this cannot be done, as an...

www.mrexcel.com