Find and Copy down macro
 

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,

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
 

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,

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
 

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!