Expected: end of statement in Macro

**reltub** · 10-16-2012, 05:09 PM

Hi all -

I have a macro that is supposed to communicate with Bloomberg to retrieve some static info from certain fields. The macro looks through my excel sheet that has columns A & B (Cusip and AbsRefID, respectively), and basically concatenates it into cell C so that Bloomberg can read it. Then I retrieve data points using Bloomberg's BDP function for each column. I'm getting an expected end of statement error in the highlighted portion of the code below. Any help is greatly appreciated.

Please Login or Register  to view this content.

**HaHoBe** · 10-16-2012, 11:51 PM

Hi, reltub,

when I paste your code into a module the lines with the formulas to be inserted are highlighted.

You would need to double up the quotes there from (just the first line here):

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Ciao,
Holger

**reltub** · 10-17-2012, 01:47 PM

Thanks for the quick reply Holger. I tried your suggestion, but it still didn't work. The formula for cells in column D would just display =BDP("C","CALLED_DT"). I need it to actually reference what is in column C and use that in the formula. I revised the code a little and here's what I have now. I'm having the same problem though. Formula in column D is displaying as =BDP("rng3.Value","CALLED_DT").

Please Login or Register  to view this content.

**HaHoBe** · 10-17-2012, 02:04 PM

Hi, reltub,

I must excuse - it was way to early this morning to answer.

Please Login or Register  to view this content.

for the Value of the cell and

Please Login or Register  to view this content.

for the address with absolute reference.

Ciao,
Holger

**reltub** · 10-17-2012, 03:58 PM

Works like a charm with .address! Thanks!
I'm new to working with BB and with ranges like this, so please excuse any ignorance. In step 2 of my macro, I look at data that was pulled in from BB and delete certain rows based on criteria. The errors I get in this step are included in the comments in the code. I don't understand why I am getting a data type mismatch error for the Month(Date) criteria, which deletes rows where column D is not in the current month. I also don't understand why I'm getting an object required error for the <> "CALLED IN FULL" part of the code. This deletes rows where column F is not equal to CALLED IN FULL. Here is the code:

Please Login or Register  to view this content.

**HaHoBe** · 10-17-2012, 11:50 PM

Hi, reltub,

could you please attach a workbook with some sample data for a check? That would life a lot easier at least for me and especially at this time of the day.

Ciao,
Holger

**reltub** · 10-18-2012, 09:28 AM

Sure thing. I've attached a sample worksheet. The formulas won't work because you have to be connected to a Bloomberg terminal, but you can see what I'm trying to do. Thanks for your help!

**HaHoBe** · 10-18-2012, 10:46 AM

Hi, reltub,

as I don´t have access to Blomberg it´s hard to tell. But we´ll try anyway. If you compare such a lot of lines to maybe be deleted I´ll try a slightly different setup as you have it - if your first check proves true the respective row is deleted but all further checks will be run as well.

Let´s take the first part like

Please Login or Register  to view this content.

Maybe you try to alter it to

Please Login or Register  to view this content.

I don´t know if that will help with the problem you encountered but when a criteria is met and the row is deleted the code will continue on the next row and not fulfill further checks for a row that maybe have been done before as well.

Ciao,
Holger

**reltub** · 10-18-2012, 12:46 PM

That is very fast and works, but it doesn't loop through the entire worksheet. I have to hit run for it to check each line. Is there something I'm missing here?

**HaHoBe** · 10-18-2012, 12:49 PM

Hi, reltub,

you had a code which loops from the last line to the top. Insode that code there´s a passge I cited which maybe could be replaced by the code I indicated. And if that works you could replace the other parts as well in the very same way.

Ciao,
Holger

**reltub** · 10-18-2012, 01:04 PM

So the new code could be somethin like this:

Please Login or Register  to view this content.

**HaHoBe** · 10-18-2012, 01:16 PM

Hi, reltub,

I think your compiler wouldn´t like that code sniplet (missing Next for both loops).

Maybe the code may look like this:

Please Login or Register  to view this content.

Ciao,
Holger

**reltub** · 10-18-2012, 01:30 PM

That makes sense, but for some reason it still isn't looping. It will find the last row where the description ends in Corp and delete it if the criteria are met, but it stops after that.

**HaHoBe** · 10-18-2012, 01:41 PM

Hi, reltub,

now that was all my fault because of teh Exit For.

Let´s try it with a slightly modified version:

Please Login or Register  to view this content.

Ciao,
Holger

**reltub** · 10-18-2012, 01:50 PM

That did it. I didn't notice that Exit For in there the first time, thanks. It it taking forever to go through the worksheet and test the criteria, but the good news is that I know why. Every time it deletes a row, all rows below shift up and thus, recalculate based on that BDP formula it inserted into the ranges in Step1. I need to copy and paste special values into G, H, and I, then delete the columns D, E, and F to speed this thing up.

**HaHoBe** · 10-18-2012, 02:00 PM

Hi, reltub,

another option would be to put Application.Calculation = xlCalculationManual before the code and Application.Calculation = xlCalculationAutomatic at the end - by this calculation is only done once after the macro has finished deleting.

Ciao,
Holger

**reltub** · 10-18-2012, 02:36 PM

much, much better than using the clipboard! thank you for all your help, Holger!

**reltub** · 10-18-2012, 04:10 PM

Is this not the proper way to check if the date in C is in the current month? It is deleting everything on the sheet.

rng4.Value <> Month(Date)

**HaHoBe** · 10-18-2012, 09:26 PM

Hi, reltub,

I mentioned earlier on that I don´t have access to Blomberg so I don´t know what´s the value of rng4.Value. I know for sure that Month(Date) will deliver 10 (a digit) for this month. If it´s a date in rng4 you may use Month(rng4.Value) but maybe it´s a string/text that´s inside that cell.

Let me give you a small example on that:

Please Login or Register  to view this content.

Please indicate what´s the content of a rng4.Value if this won´t help.

Ciao,
Holger

**reltub** · 10-18-2012, 09:42 PM

rng4.value returns a date if a security has a call date, otherwise the cells will show "#n/a not applicable" if it does have a date, I want to see if the date is in the current month, if not I want to delete the entire row.

**reltub** · 10-19-2012, 11:28 AM

I get a type mismatch error when I use Month(rng4.Value) <> Month(Date). I can't figure out why it's doing this. For example, this macro should be deleting any row where the date in column D is not in the current month. The row below should be deleted:

313375CP0 AGY230STEPUP 313375CP0 Corp 8/22/2012 100 .5231

Please Login or Register  to view this content.

**HaHoBe** · 10-19-2012, 02:00 PM

Hi, reltub,

I hve to copy the line from this articel and paste it into the workbook you supplied. Although I use a different country setting as you the date is recognized as this by my system and thus the line is deleted. Right now I ran out of ideas of how to help solving this problem. Let´s see what this weekend brings (except extended coding

).

Ciao,
Holger

**reltub** · 10-19-2012, 09:32 PM

Finally fixed this before I left work today. The code is basically fine, just had one small detail I was missing. I forgot to check and see if rng4 was actually something excel considered a date. Some cells contained #N/A and thus, confused excel. So I added this to the beginning of the code and it works great! thanks for all your help! I'm having another issue in another part of the code, but i'll save that for a new post! Have a good weekend!!

Please Login or Register  to view this content.

**Cutter** · 10-20-2012, 05:57 PM

@ reltub

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Expected: end of statement in Macro

LinkBack

Thread Tools

Rate This Thread

Display

Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Re: Expected: end of statement in Macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions