"Application Defined or Object-defined Error"

[steven426]

I am getting an error in the debugging for this code, and cannot for the life of my figure out why.

The part that is giving an error is
Range(Cells(2, DEAL_TYPE), Cells(NUM_POS, DEAL_TYPE)).Formula = "=BLP(RC""&"" MTGE"",""MTG_DEAL_TYP"")"

This is supposed to put a formula into cells 9,2 to 9,76 which is I9:I76 (Deal Type is 9 and Num_Pos is 76)

Here is the code (note, this requires knowledge of BLP (Bloomberg) to understand




Sub Pull_Data()
'This define the titles on the Master List sheet of the workbook as strings of characters
Dim NUM_POS As Long
Dim cusip_type As String
Dim description_pos As String
Dim open_orig As String
Dim inv_type As String
Dim offer_type As String
Dim payout As String
Dim offer As String
Dim DEAL_TYPE As String
Dim SECURITY_TYPE As String
Dim Agency As String
Dim CMO As String
Dim Pool As String
Dim AGENCY_CMO As String
Dim AGENCY_POOL As String
Dim Category As String
Shtname = "Master List"
Sheets(Shtname).Select
'This counts the number of bonds we have on the master list
Num_Master = Application.WorksheetFunction.CountA(Worksheets(Shtname).Range("B:B"))
'This defines the cells on the Master List sheet that we will be using in subsequent functions
cusip_type = Application.WorksheetFunction.Match("cusip_type", Worksheets(Shtname).Range("1:1"), 0)
description_pos = Application.WorksheetFunction.Match("description_pos", Worksheets(Shtname).Range("1:1"), 0)
open_orig = Application.WorksheetFunction.Match("open_orig", Worksheets(Shtname).Range("1:1"), 0)
inv_type = Application.WorksheetFunction.Match("inv_type", Worksheets(Shtname).Range("1:1"), 0)
offer_type = Application.WorksheetFunction.Match("offer_type", Worksheets(Shtname).Range("1:1"), 0)
payout = Application.WorksheetFunction.Match("payout", Worksheets(Shtname).Range("1:1"), 0)
offer = Application.WorksheetFunction.Match("offer", Worksheets(Shtname).Range("1:1"), 0)
DEAL_TYPE = Application.WorksheetFunction.Match("DEAL_TYPE", Worksheets(Shtname).Range("1:1"), 0)
SECURITY_TYPE = Application.WorksheetFunction.Match("SECURITY_TYPE", Worksheets(Shtname).Range("1:1"), 0)
Agency = Application.WorksheetFunction.Match("Agency", Worksheets(Shtname).Range("1:1"), 0)
CMO = Application.WorksheetFunction.Match("CMO", Worksheets(Shtname).Range("1:1"), 0)
Pool = Application.WorksheetFunction.Match("Pool", Worksheets(Shtname).Range("1:1"), 0)
AGENCY_CMO = Application.WorksheetFunction.Match("AGENCY_CMO", Worksheets(Shtname).Range("1:1"), 0)
AGENCY_POOL = Application.WorksheetFunction.Match("AGENCY_POOL", Worksheets(Shtname).Range("1:1"), 0)
Category = Application.WorksheetFunction.Match("Category", Worksheets(Shtname).Range("1:1"), 0)
NUM_POS = Application.WorksheetFunction.CountA(Worksheets(Shtname).Range("B:B"))
'This pulls in the Deal Type from Bloomberg
Range(Cells(2, DEAL_TYPE), Cells(NUM_POS, DEAL_TYPE)).Formula = "=BLP(RC""&"" MTGE"",""MTG_DEAL_TYP"")"

 

[schielrn]

I would assume your quotes are probably wrong, because based on how you have it the formula would enter as:

=BLP(RC"&" MTGE","MTG_DEAL_TYP")

I believe. What is the formula suppose to look like in the workbook?

 

[steven426]

It is supposed to be

=BLP(B2&" MTGE","MTG_DEAL_TYP")

Thanks

 

[schielrn]

Try this:

Range(Cells(2, DEAL_TYPE), Cells(NUM_POS, DEAL_TYPE)).Formula = "=BLP(RC&"" MTGE"",""MTG_DEAL_TYP"")"

I don't have Bloomberg, but the formula looks correct in the address bar, but not sure if those are named ranges and if you actually need the quotes in the last part?

Hope that helps.

 

[steven426]

Nope...
If it helps, it you were actually typing this into bloomberg, you would inter the bond (which is the contents of rc) then space "mtge" The "MTG_DEAL_TYP" is built in to the function to retrieve the deal type. That is not actually typed.

 

[schielrn]

Are you still getting the error or is the formula not being input properly because how you have it typed out is how it shows up in my sheet other than if you want column B in the formula and not column I then you need this:

Range(Cells(2, DEAL_TYPE), Cells(NUM_POS, DEAL_TYPE)).Formula = "=BLP(RC[-7]&"" MTGE"",""MTG_DEAL_TYP"")"
Hope that helps.