Am new on macros and need assistance to adjust the macro below for
undetermined ranges

Dim rng As Range
Dim i As Integer

With Worksheets("sheet1")
Set rng = .Range("a2:b8")
End With

For i = 1 To 7
With Worksheets("sheet2") 'assuming the data you want goes to column b
and
'the lookup value is in Column A'
..Cells(1 + i, 2) = Application.WorksheetFunction.VLookup(.Cells(1 + i,
1), rng, 2, 1)
End With

Next i

what does this code do?
what do you want it to do?
what do you mean by undetermined range?

"sylink" wrote:

Am new on macros and need assistance to adjust the macro below for
undetermined ranges

Dim rng As Range
Dim i As Integer

With Worksheets("sheet1")
Set rng = .Range("a2:b8")
End With

For i = 1 To 7
With Worksheets("sheet2") 'assuming the data you want goes to column b
and
'the lookup value is in Column A'
..Cells(1 + i, 2) = Application.WorksheetFunction.VLookup(.Cells(1 + i,
1), rng, 2, 1)
End With

Next i

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

OK I might understand the question.

You do not know the number of rows of information in Sheet 1

One way to loop through is:

Dim myCell as Range

With Activeworkbook.Sheets('Data")

For Each myCell in .Range(.Cells(3,1),.Cells(60000,1).End(XLUp))
'''your search code here using myCell as the cell.value you are looking up
next myCell

End With


"sylink" wrote:

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