Execute all formulas before convert to values

I'm executing two lines of code to pull data with BDP Formulas from Bloomberg (I have about 95,000 lines of data). The problem which is occuring is the "convert cells to values" line of code occurs before all the formulas have finished pulling in the data from Bloomberg. Is there a way to delay the "convert cells to values" line of code to ensure the data formulas have finished pulling the data first?

Sub BloombergFormula()

Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Sheets("FullFile")
        .Range("P2:P" & LastRow).FormulaR1C1 = "=IF(LEFT(IF(RC[-2]=""Cusip""," & _
            "RC[-3],IF(RC[-1]="""","""",BDP(RC[-1],""ID_CUSIP""))),4)=""#N/A"",""""," & _
            "IF(RC[-2]=""Cusip"",RC[-3],IF(RC[-1]="""","""",BDP(RC[-1],""ID_CUSIP""))))"
        .Range("Q2:Q" & LastRow).FormulaR1C1 = "=IF(LEFT(BDP(RC[-2],""ID_ISIN""),4)=""#N/A"","""",BDP(RC[-2],""ID_ISIN""))"
        .Range("P2:Q" & LastRow) = .Range("P2:Q" & LastRow).Value
    End With

End Sub

I wouldn't think that was possible, but I'm not familiar with the BDP functions.

This may wait until the formulas have completed calculating.

Do Until Application.CalculationState = xlDone: DoEvents: Loop

I tried that but the formulas aren't finishing the calculation process before executing the line to convert the cells to values. Do I have the new line in the correct part of the code?

Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Sheets("FullFile")
        .Range("P2:P" & LastRow).FormulaR1C1 = "=IF(LEFT(IF(RC[-2]=""Cusip""," & _
            "RC[-3],IF(RC[-1]="""","""",BDP(RC[-1],""ID_CUSIP""))),4)=""#N/A"",""""," & _
            "IF(RC[-2]=""Cusip"",RC[-3],IF(RC[-1]="""","""",BDP(RC[-1],""ID_CUSIP""))))"
        .Range("Q2:Q" & LastRow).FormulaR1C1 = "=IF(LEFT(BDP(RC[-2],""ID_ISIN""),4)=""#N/A"","""",BDP(RC[-2],""ID_ISIN""))"
        Do Until Application.CalculationState = xlDone: DoEvents: Loop
        .Range("P2:Q" & LastRow) = .Range("P2:Q" & LastRow).Value
    End With

End Sub

This will wait 3 seconds. Adjust the time to suit.

'Top of code module
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
Sub BloombergFormula()
    
    Dim LastRow As Long, d As Date
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Sheets("FullFile")
        .Range("P2:P" & LastRow).FormulaR1C1 = "=IF(LEFT(IF(RC[-2]=""Cusip""," & _
            "RC[-3],IF(RC[-1]="""","""",BDP(RC[-1],""ID_CUSIP""))),4)=""#N/A"",""""," & _
            "IF(RC[-2]=""Cusip"",RC[-3],IF(RC[-1]="""","""",BDP(RC[-1],""ID_CUSIP""))))"
        .Range("Q2:Q" & LastRow).FormulaR1C1 = "=IF(LEFT(BDP(RC[-2],""ID_ISIN""),4)=""#N/A"","""",BDP(RC[-2],""ID_ISIN""))"
        
        d = Now + TimeValue("00:00:03"): Do Until d < Now: DoEvents: Sleep 100: Loop
        
        .Range("P2:Q" & LastRow) = .Range("P2:Q" & LastRow).Value
    End With
    
    MsgBox "Done"
End Sub