Hi guys. the spreadsheet i am currently using pulls in data from bloomberg. in order to pull in the data from bloomberg, and run my calculations off said data, i have had to use the Ontime Now +TimeValue funciton in vba in order for the macro to wait for the bloomberg data to be pulled in before making any calculations. here is the doe that i am using:
Code
Option Explicit
Option Compare Text
Const s10 As String = "=IF(Config!$E$1=TRUE,IF(Trade_type=""Fut"","""",BDP(""cl""&month&"" comdty"",""px_settle"")),IF(Trade_type=""Fut"","""",BDP(""cl""&month&"" comdty"",""last price"")))"
Const s11 As String = "=IF(Config!$E$1=TRUE,IF(Trade_type=""fut"",BDP(""cl""&month&"" comdty"",""px_settle""),BDP(""cl""&month&c_p&"" ""&strike&"" comdty"",""px_settle"")),IF(Trade_type=""fut"",BDP(""cl""&month&"" comdty"",""last price""),BDP(""cl""&month&c_p&"" ""&strike&"" comdty"",""last price"")))"
Sub Fetch_wti_pnl()
Dim i As Integer
With Application
.ScreenUpdating = False
.OnTime Now + TimeValue("00:00:05"), "wti_pnl_calc"
End With
With Sheets("WTI")
For i = 3 To 14000
If .Cells(i, 1) = 1 Then
With .Cells(i, 1).Offset(, 8)
.Formula = s10
End With
With .Cells(i, 1).Offset(, 10)
.Formula = s11
End With
End If
Next
End With
Application.Calculation = xlCalculationAutomatic
End Sub
Display More
i would like to convert where it says TimeValue("00:00:05") to something more dynamic and linked to a range in the spreadsheet. so instead of TimeValue("00:00:05"), something like TimeValue(range("timevalue").select). BTW i tried to enter a range name and it didnt work.
Any help would be greatly appreciated. Thank you.