Find and copy down Macro
 

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.

"Barry McConnell" wrote:

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" wrote:

Barry,

The macro looks for #NA errors and I'm a bit confused by you saying you have
"#N/A N/A"
in your cell. How is this value derived?

Mike

"Barry McConnell" wrote:

Thanks Mike, although it doesn't seem to have any effect. Maybe I have copied
it in wrongly. Also the dataset is across almost all the columns and down
around 350 rows and the "#N/A N/A" value can be dotted around anywhere. So
something that does what I need regardless of column would be ideal. The
reason the values are there is because they are stock prices. Some days there
is no price and the previous days last closing price needs to be copied down
from above.


"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Change the column
to suit

Sub sonic()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
If WorksheetFunction.IsNA(c.Value) Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub

Mike

"Barry McConnell" wrote:

Hello

I just need some help with a small macro. I need it to find any values in a
sheet that have the value "#N/A N/A". The macro then need to copy down the
value immediately above it in the column. Also, to make it more tricky it
needs to copy down to replace all the "#N/A N/A" that are below the number
above any "#N/A N/A". So for example it might be

74
"#N/A N/A"
78

And I need it to be

74
74
78

and sometimes it is

74
"#N/A N/A"
"#N/A N/A"
78

and need it to be

74
74
74
78

Thanks for your help!

Find and copy down Macro
 

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

"Barry McConnell" wrote:

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.

"Barry McConnell" wrote:

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" wrote:

Barry,

The macro looks for #NA errors and I'm a bit confused by you saying you have
"#N/A N/A"
in your cell. How is this value derived?

Mike

"Barry McConnell" wrote:

Thanks Mike, although it doesn't seem to have any effect. Maybe I have copied
it in wrongly. Also the dataset is across almost all the columns and down
around 350 rows and the "#N/A N/A" value can be dotted around anywhere. So
something that does what I need regardless of column would be ideal. The
reason the values are there is because they are stock prices. Some days there
is no price and the previous days last closing price needs to be copied down
from above.


"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Change the column
to suit

Sub sonic()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
If WorksheetFunction.IsNA(c.Value) Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub

Mike

"Barry McConnell" wrote:

Hello

I just need some help with a small macro. I need it to find any values in a
sheet that have the value "#N/A N/A". The macro then need to copy down the
value immediately above it in the column. Also, to make it more tricky it
needs to copy down to replace all the "#N/A N/A" that are below the number
above any "#N/A N/A". So for example it might be

74
"#N/A N/A"
78

And I need it to be

74
74
78

and sometimes it is

74
"#N/A N/A"
"#N/A N/A"
78

and need it to be

74
74
74
78

Thanks for your help!

Find and copy down Macro
 

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

"Mike H" wrote:

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

"Barry McConnell" wrote:

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.

"Barry McConnell" wrote:

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" wrote:

Barry,

The macro looks for #NA errors and I'm a bit confused by you saying you have
"#N/A N/A"
in your cell. How is this value derived?

Mike

"Barry McConnell" wrote:

Thanks Mike, although it doesn't seem to have any effect. Maybe I have copied
it in wrongly. Also the dataset is across almost all the columns and down
around 350 rows and the "#N/A N/A" value can be dotted around anywhere. So
something that does what I need regardless of column would be ideal. The
reason the values are there is because they are stock prices. Some days there
is no price and the previous days last closing price needs to be copied down
from above.


"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Change the column
to suit

Sub sonic()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
If WorksheetFunction.IsNA(c.Value) Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub

Mike

"Barry McConnell" wrote:

Hello

I just need some help with a small macro. I need it to find any values in a
sheet that have the value "#N/A N/A". The macro then need to copy down the
value immediately above it in the column. Also, to make it more tricky it
needs to copy down to replace all the "#N/A N/A" that are below the number
above any "#N/A N/A". So for example it might be

74
"#N/A N/A"
78

And I need it to be

74
74
78

and sometimes it is

74
"#N/A N/A"
"#N/A N/A"
78

and need it to be

74
74
74
78

Thanks for your help!

Find and copy down Macro
 

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" wrote:

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

"Barry McConnell" wrote:

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.

"Barry McConnell" wrote:

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" wrote:

Barry,

The macro looks for #NA errors and I'm a bit confused by you saying you have
"#N/A N/A"
in your cell. How is this value derived?

Mike

"Barry McConnell" wrote:

Thanks Mike, although it doesn't seem to have any effect. Maybe I have copied
it in wrongly. Also the dataset is across almost all the columns and down
around 350 rows and the "#N/A N/A" value can be dotted around anywhere. So
something that does what I need regardless of column would be ideal. The
reason the values are there is because they are stock prices. Some days there
is no price and the previous days last closing price needs to be copied down
from above.


"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Change the column
to suit

Sub sonic()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
If WorksheetFunction.IsNA(c.Value) Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub

Mike

"Barry McConnell" wrote:

Hello

I just need some help with a small macro. I need it to find any values in a
sheet that have the value "#N/A N/A". The macro then need to copy down the
value immediately above it in the column. Also, to make it more tricky it
needs to copy down to replace all the "#N/A N/A" that are below the number
above any "#N/A N/A". So for example it might be

74
"#N/A N/A"
78

And I need it to be

74
74
78

and sometimes it is

74
"#N/A N/A"
"#N/A N/A"
78

and need it to be

74
74
74
78

Thanks for your help!

Find and copy down Macro
 

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


"Barry McConnell" wrote:

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" wrote:

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

"Barry McConnell" wrote:

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.

"Barry McConnell" wrote:

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" wrote:

Barry,

The macro looks for #NA errors and I'm a bit confused by you saying you have
"#N/A N/A"
in your cell. How is this value derived?

Mike

"Barry McConnell" wrote:

Thanks Mike, although it doesn't seem to have any effect. Maybe I have copied
it in wrongly. Also the dataset is across almost all the columns and down
around 350 rows and the "#N/A N/A" value can be dotted around anywhere. So
something that does what I need regardless of column would be ideal. The
reason the values are there is because they are stock prices. Some days there
is no price and the previous days last closing price needs to be copied down
from above.


"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Change the column
to suit

Sub sonic()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
If WorksheetFunction.IsNA(c.Value) Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub

Mike

"Barry McConnell" wrote:

Hello

I just need some help with a small macro. I need it to find any values in a
sheet that have the value "#N/A N/A". The macro then need to copy down the
value immediately above it in the column. Also, to make it more tricky it
needs to copy down to replace all the "#N/A N/A" that are below the number
above any "#N/A N/A". So for example it might be

74
"#N/A N/A"
78

And I need it to be

74
74
78

and sometimes it is

74
"#N/A N/A"
"#N/A N/A"
78

and need it to be

74
74
74
78

Thanks for your help!