how do I search thru the values in my column and replace with nessesary values

[Za1]

hi

my request would be abit confusing so forgive me if i dont describe i
properly,
currently i have a few funds i need to sort and im getting raw data fro
the dealer so the names in bold are the name of the compnay and the one
unbold are the products. i have abrevations for the names of the compan
which is is column B and i use a vlookup to extract them from my ow
database the thing i wanna know is whether isit possible to change th
#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 ne
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 detec
#n/a change to AD and so on.

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

+-------------------------------------------------------------------
|Filename: New Microsoft Excel Worksheet.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=600
+-------------------------------------------------------------------

 

[Don Guillett]

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|

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

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