Hi Terry
sure it can be done. Use the following code

-----
Sub change_data()
Dim target_wks As Worksheet
Dim lookup_wks As Worksheet
Dim lookup_rng As Range
Dim row_index As Long
Dim last_row As Long
Dim ret_lookup

Set target_wks = ActiveSheet
Set lookup_wks = Worksheets("lookup_sheet")
Set lookup_rng = lookup_wks.Range("A1:B30")

last_row = target_wks.Cells(Rows.Count, "A").End(xlUp).row
For row_index = 1 To last_row
With target_wks
If .Cells(row_index, "A").Value < "" Then
On Error Resume Next
ret_lookup = Application.WorksheetFunction.VLookup _
(.Cells(row_index, "A").Value, lookup_rng, 2, 0)
If Err.Number < 0 Then
ret_lookup = "N/A"
End If
On Error GoTo 0
.Cells(row_index, "B").Value = ret_lookup
End If
End With
Next
End Sub

-----

Assumption:
- the sheet with the references is called lookup_sheet (change this in
the code to your needs)
- the values are in columns A+B


--
Regards
Frank Kabel
Frankfurt, Germany

Frank

I wonder if I might ask a further question ? Can the code be written
such that it refers to a named file which lists out all the items
that
need updating on the current spreadsheet.

I.e.

Start at current spreadsheet, line 1
Go to reference sheet and compare line 1 against the list in the
reference sheet and make changes as necessary.
Then go to line 2 and so on.... ?

Regards

Terry


---
Message posted from http://www.ExcelForum.com/