Bloomberg DDE problem
 

I'm getting the Bloomberg name and last_price via a DDE link using the
BLP and BLPSH formula. The code is below.

My problem is that for no apparent reason I get #REF! scattered around
in the cells. I can enter the same formula the #REF! cell contains in
a new Excel sheet and come up with the correct data. Does anybody have
a hint as to why this is happening?

I'm pulling my hair out over this one.

' Set Bloomberg Formulas
ActiveSheet.Range("AF7").Select
Do Until IsEmpty(ActiveCell.Offset(0, -5).Value) 'Look at Index
column
If Not IsEmpty(ActiveCell.Offset(0, -1).Value) Then ' If
Security name is not empty
'Set Bloomberg Name
strBloombergFormula = "=BLP(""" & ActiveCell.Offset(0,
1).Value & """,""NAME"")"
ActiveCell.Formula = strBloombergFormula
'Set Bloomberg Price
If Not IsEmpty(ActiveCell.Offset(0, 2).Value) Then
strBloombergFormula = "=BLPSH(""" &
ActiveCell.Offset(0, 1).Value & """,""LAST PRICE"",""" &
ActiveCell.Offset(0, 2).Value & """)"
ActiveCell.Offset(0, 3).Formula = strBloombergFormula
End If
'Set Check_1 Price
If Not IsEmpty(ActiveCell.Offset(0, 4).Value) Then
strBloombergFormula = "=BLPSH(""" &
ActiveCell.Offset(0, 1).Value & """,""LAST PRICE"",""" &
ActiveCell.Offset(0, 4).Value & """)"
ActiveCell.Offset(0, 5).Formula = strBloombergFormula
End If
'Set Check_2 Price
If Not IsEmpty(ActiveCell.Offset(0, 6).Value) Then
strBloombergFormula = "=BLPSH(""" &
ActiveCell.Offset(0, 1).Value & """,""LAST PRICE"",""" &
ActiveCell.Offset(0, 6).Value & """)"
ActiveCell.Offset(0, 7).Formula = strBloombergFormula
End If
'Set Check_3 Price
If Not IsEmpty(ActiveCell.Offset(0, 8).Value) Then
strBloombergFormula = "=BLPSH(""" &
ActiveCell.Offset(0, 1).Value & """,""LAST PRICE"",""" &
ActiveCell.Offset(0, 8).Value & """)"
ActiveCell.Offset(0, 9).Formula = strBloombergFormula
End If
'Set Check_4 Price
If Not IsEmpty(ActiveCell.Offset(0, 10).Value) Then
strBloombergFormula = "=BLPSH(""" &
ActiveCell.Offset(0, 1).Value & """,""LAST PRICE"",""" &
ActiveCell.Offset(0, 10).Value & """)"
ActiveCell.Offset(0, 11).Formula = strBloombergFormula
End If
'Set comment in Ending Equity column
'' ActiveCell.Offset(0, -7).Value = "BLOOM"

End If

ActiveCell.Offset(1, 0).Select
Loop

'Active the Bloomberg DDE Link.
Application.Run "BLPCreate"