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!