CODEWITHSUNDEEP
vbaexcelexcel-formulabloomberg
Jon0311
Jon0311

Reputation: 61

Using VBA to place multiple formulas in one cell

I'm working on a macro to place multiple formulas in one cell. In this case, its 3 Bloomberg formulas into one cell. The formula works in Excel when copied or typed manually but not when type it in the VBA module, where it creates an error (Compile error: Expected: End of statement) and highlights "rtg_mdy_sen_unsecured_debt". I believe the problem is the use of quotation marks in the formula but I can't find any information on how to work around it.

The formula that works in Excel (when input manually or copied into a cell) follows:
=BDP($O4,"rtg_mdy_sen_unsecured_debt")&"/"&BDP($O4,"rtg_sp_lt_lc_issuer_credit")&"/"&BDP($O4,"rtg_fitch_sen_unsecured")

It does not work in VBA when attempting the following format:

Cells(4, 21).Value = "=BDP($O4,"rtg_mdy_sen_unsecured_debt")&"/"&BDP($O4,"rtg_sp_lt_lc_issuer_credit")&"/"&BDP($O4,"rtg_fitch_sen_unsecured")"

Simpler Bloomberg formulas work just fine: ($O4 references a cell which contains "BA Equity". Q2 contains "industry_sector")

Cells(4, 17).Value = "=BDP(O4,Q2)"

The desired end state is that Cells(4, 21) will display Boeing's credit rating from the three major rating agencies, like this: A2/A/A

Upvotes: 0

Views: 1634

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

When creating VBA strings that contain " characters you must delimit them with another "

This will work for the poseted string

Cells(4, 21).Value = _
"=BDP($O4,""rtg_mdy_sen_unsecured_debt"")&""/""&BDP($O4,""rtg_sp_lt_lc_issuer_credit"")&""/""&BDP($O4,""rtg_fitch_sen_unsecured"")"

Upvotes: 2

Related Questions