loop thru rows and populate a cell based on another cell's content

[pdeshazier]

how do i loop thru all records and use a select case statement to populate a cell based on an existing cell's value?
for instance, i have a workbook containing company name, but i want to populate a cell on the same row with the company ID number which would be set in my select case statement.

in the attached sample file, i need column A read and based on the contents of each A cell, need the following numeric values populated in the next available column.

col A value new column value
SUMNER REG MED CTR 16750
TROUSDALE MED CTR 16751
RIVERVIEW REG MED CTR 16753
FAMILY PHYSICIAN PRACTIC 16754
HIGHPOINT CORP OFFICE 16755
HIGHPOINT HOMECARE 16756
HIGHPOINT HOSPICE 16767

[ranman256]

you should use VLOOKUP in the cell to produce the #...but if you want to do the scan method...


Sub ScanRows()
'
range("A3").select
While ActiveCell.Value <> ""

select case activecell.value
case "HIGHPOINT CORP OFFICE"
activecell.offset(0,1).value = "16755"
case "SUMNER REG MED CTR"
activecell.offset(0,1).value = "16750"
end select

ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub