hi

my request would be abit confusing so forgive me if i dont describe it properly,
currently i have a few funds i need to sort and im getting raw data from the dealer so the names in bold are the name of the compnay and the ones unbold are the products. i have abrevations for the names of the company which is is column B and i use a vlookup to extract them from my own database the thing i wanna know is whether isit possible to change the #n/A values in column B to reflect which product is from which company. like cell b3 to change its value automatically till the it hits a new value for example cell b5. eg b2 AGB, b3 detects #N/A changes to AGB, b4detects #N/A changes to AGB, b5 detects AD changes to AD, b6 to detect #n/a change to AD and so on.

im sorry if this is a confusing post hope to get some help soon

On Wednesday, September 26, 2012 4:10:01 AM UTC-5, Za1 wrote:
hi



my request would be abit confusing so forgive me if i dont describe it

properly,

currently i have a few funds i need to sort and im getting raw data from

the dealer so the names in bold are the name of the compnay and the ones

unbold are the products. i have abrevations for the names of the company

which is is column B and i use a vlookup to extract them from my own

database the thing i wanna know is whether isit possible to change the

#n/A values in column B to reflect which product is from which company.

like cell b3 to change its value automatically till the it hits a new

value for example cell b5. eg b2 AGB, b3 detects #N/A changes to AGB,

b4detects #N/A changes to AGB, b5 detects AD changes to AD, b6 to detect

#n/a change to AD and so on.



im sorry if this is a confusing post hope to get some help soon





+-------------------------------------------------------------------+

|Filename: New Microsoft Excel Worksheet.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=600|

+-------------------------------------------------------------------+







--

Za1

Save as .xls or .xlsM and use this macro

Sub ChangeNA_SAS()
Dim lr As Long
Dim c As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("b2:b" & lr)
If IsError(c) Then c.Value = c.Offset(-1)
Next c
End Sub

thankyou so much it worked