I need to compare the sheet1 and sheet2 and fill the date values in
sheet1 . The range is undetermined in that the no of row in both sheet1
and sheet2 is not known.


sheet1 sheet2
Names Date Names
Date
===== ====
AAICDLTD AAICDLTD
11-Jan-01
ABALUF ABANITO
27-Jul-01
ABIMACJ ABALUF
28-Aug-02
ADESEUN ABIMACJ
19-Nov-01
ADESEUN
12-Dec-02
Some suggested the code below, but would not work for an unknown no of
rows.

Sub Button1_Click() Dim rng As Range Dim i As Integer With
Worksheets("Data") Set rng = .Range("a3:b5") 'make as long as you need
End With For i = 1 To 3 ' how ever many cells there are With
Worksheets("Results") 'assuming the data you want goes to column b and
the lookup value is in Column A' .Cells(2 + i, 2) =
Application.WorksheetFunction.VLookup(.Cells(2 + i, 1), rng, 2, 1) End
With Next i End Sub