Multi tool use

For loop in VBA when refreshing Bloomberg requests

I am putting together an excel workbook that performs various calculations based on a number of Bloomberg fields. For a particular date, it checks various requirements and then outputs either a 1 or -1 in a row based on if the requirements are met or not. I have got this working with a macro.

The issue is I need to run the checks through a number of dates. So each loop I need the input date to be changed, the Bloomberg data needs to refresh and then either put a 1 or -1 in the next row down based on if requirements are met for this date.

The problem is I can not get the loop to work correctly. Below is a simplified version of my code:

Public i As Integer

Public Sub RefreshStaticLinks()

Call Worksheets("Sheet2").Range("A5:H7").Select
Call Application.Run("RefreshCurrentSelection")
Call Application.OnTime(Now + TimeValue("00:00:10"), "ProcessData")

End Sub


Private Sub ProcessData()

Dim c As Range
Dim b As Range

For Each c In Selection.Cells
If c.Value = "#N/A Requesting Data..." Then
Call Application.OnTime(Now + TimeValue("00:00:5"), "ProcessData")
Exit Sub
End If
Next c

i = 6

'Column A has a list of dates that I want to use to update the input date
For Each b In Worksheets("Sheet2").Range("A6:A20").Cells
Worksheets("Sheet2").Range("H2").Value = b.Value

[some requirements for the values to meet certain thresholds] Then
Worksheets("Sheet2").Cells(i, 13).Value = "1"

Else:
Worksheets("Sheet2").Cells(i, 13).Value = "-1"
End If

i = i + 1
Next b

End Sub


Does anyone know a way of dealing with problem? Thanks

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.