Find and copy down Macro

[Barry McConnell]

This is a message from a few days ago. Didn't get an newer macro from the
person that was helping me. All help greatfully received.

:

Oh right, that's because the spreadsheet is connected to a live Bloomberg
feed, then the data is copied and pasted as a values. When there is not a
price on a given day, the value "#N/A N/A" is returned. So I suppose it
should be looking for a text string perhaps. Sorry for the confusion.

 

[Mike H]

Barry,

To try and resolve this I set up a Web query in Excel from Bloomberg at this
address
http://www.bloomberg.com/markets/stocks/futures.html

As you will see on the futures table there are some N.A. values but these
copy to my sheet exactly like that so if I change my macro to

If c.Value = "N.A." Then

Then it works for me but I can't replicate the error value you are getting.
Where are you downloading from? Are you using Data Import Web query or
something else?

Mike

 

[Mike H]

Barry,

I should have added that despite being able to copy down values with the
macro as soon as the web query refreshed it changed back to N.A.

Mike

 

[Barry McConnell]

Hello again Mike, thanks again for looking at this. The data accessed from a
Bloomberg terminal, not a web query.

The important thing is that they are all converted into values and the error
message "#N/A N/A" is really just a word. So it just needs to find the word
or column of words and copy down the values from above.

Thanks

 

[Mike H]

Hi,

In which case the macro I gave you utilising this line should work

If c.Value = "#N/A N/A" Then

If it doesn't you can upload a copy of the workbook here
http://www.savefile.com/
post the link and I'm sure someone will help.

Mike