Try this.
Sub asddfa()

Dim myRange As Range
Dim lastRow As Integer
Dim c As Variant

lastRow = ActiveSheet.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myRange = ActiveSheet.Range("B2:B" & lastRow)


For Each c In myRange
c.Value = Application.VLookup(c.Offset(0, -1).Value,
Sheets("Sheet2").Range("$A$2:$D$2907"), 4, False)
Next


End Sub


"< AVG Joe" wrote:

I need to use the value from colum a on sheet 1 and populate column b on
sheet 1 from a vlookup of the value from sheet 2. I realize I could just
"copy down" but both sheets will have many and variable number of rows. Also,
would prefer to set column b to the "value" of the vloookup and not the
formula itself. I sure appreciate the help!
Here is my basic layout:
Sheet 1:
PTN GTS
123
345

Sheet 2:
PTN GTS
123 XYZ
456 ABC

CODE:
Dim myRange As Range
Dim lastRow As Integer
Dim c As Variant

lastRow = ActiveSheet.Cells(Cells.Rows.Count, "I").End(xlUp).Row
Set myRange = ActiveSheet.Range("I2:I" & lastRow) 'Change to suit

For Each c In myRange
myRange.Value = Application.VLookup("H2",
"Sheet2!$A$2:Sheet2!$D$2907", 4, False)
Next