Excel macro to get data from Bloomberg

[jonasJWS]

Hey!

I have the following macro that loops through 100 tickers in sheet 1, gets the trades for each, pushes each ticker's trades to sheet 2 to create a list of all trades. The problem is that I just get a #N/A requesting data for each ticker. This seems to be a wider problem with fetching external data from Bloomberg. However, I cannot seem to gete a fix for it. Any help would be much appreciated

Sub Macro()
On Error Resume Next
Sheet2.Range("B3:E65536").ClearContents
Application.Calculation = xlCalculationManual
For Y = 4 To Sheet1.Range("A65536").End(xlUp).Row
Sheet3.Range("C4") = Sheet1.Cells(Y, 1) & " L3 Equity"
Sheet3.Range("C9:F65536").ClearContents
Calculate
If Sheet3.Range("C9") <> "" Then
For Z = 9 To Sheet3.Range("C65536").End(xlUp).Row
Sheet2.Range("B65536").End(xlUp).Offset(1, 0) = Sheet3.Range("C4")
Sheet2.Range("C65536").End(xlUp).Offset(1, 0) = Sheet3.Cells(Z, 3)
Sheet2.Range("D65536").End(xlUp).Offset(1, 0) = Sheet3.Cells(Z, 4)
Sheet2.Range("E65536").End(xlUp).Offset(1, 0) = Sheet3.Cells(Z, 5)
Next
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

 

[VoG]

Cross posted Excel macro too fast with bloomberg request

Please read Excelguru Help Site - A message to forum cross posters