CODEWITHSUNDEEP
excelvbaexcel-formulasyntax
OMO
OMO

Reputation: 5

Inserting Bloomberg Formula into Cell

I am looking to enter the formula on line 32 into a cell but am unable to run or compile my code. There must be an quotation error or syntax that im not picking up. Could someone please explain the issue with running this code?

Thank you, Ori 

Sub Ticker_Update()

Dim subSector As String
Dim Ticker As String

Dim bRow As Double
Dim tRow As Double

Dim bRow1 As Double

Application.ScreenUpdating = False

With ThisWorkbook.Worksheets("SectorSort")

Range("C8:AA1000").Clear
subSector = Range("B6").Value

Worksheets("Canadian").Activate
    With ThisWorkbook.Worksheets("Canadian")

        tRow = 4
        bRow = Cells(Rows.Count, "C").End(xlUp).row

        Do While tRow <= bRow

            If Cells(tRow, "C").Value = subSector Then
                Ticker = Cells(tRow, "B").Value

                Worksheets("sectorSort").Activate
                    bRow1 = .Cells(Rows.Count, "C").End(xlUp).row + 1
                    .Cells(bRow1, "C") = Ticker
                    .Cells(bRow1, "D").Formula = "=BDS($C8,"CHAIN_TICKERS","CHAIN_STRIKE_PX_OVRD=ATM","CHAIN_EXP_DT_OVRD",TEXT(D$7,"YYYYMMDD"),"CHAIN_PERIODICITY_OVRD=ALL")"

                Worksheets("Canadian").Activate

                tRow = tRow + 1

            Else
                tRow = tRow + 1
            End If

    Loop

    End With

    Worksheets("SectorSort").Activate

End With


End Sub

Upvotes: 0

Views: 174

Answers (1)

toroc
toroc

Reputation: 126

Use double quotes "" inside the formula:

.Cells(bRow1, "D").Formula = "=BDS($C8,""CHAIN_TICKERS"",""CHAIN_STRIKE_PX_OVRD=ATM"",""CHAIN_EXP_DT_OVRD"",TEXT(D$7,""YYYYMMDD""),""CHAIN_PERIODICITY_OVRD=ALL"")"

Upvotes: 1

Related Questions