Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND-COPY DIFFERENT WORDS WITH MACRO | Excel Discussion (Misc queries) | |||
FIND-COPY DIFFERENT WORDS WITH MACRO | Excel Discussion (Misc queries) | |||
Macro to find matching date and copy values to another sheet | Excel Discussion (Misc queries) | |||
Macro to find copy "header" and paste | Excel Discussion (Misc queries) | |||
Macro to find, copy, and paste until value change | Excel Worksheet Functions |