Vlookup through VBA

[macro_user]

hi...
i need to use vlookup through my code in VBA...

VLOOKUP(A4,'sheet1'!F:G,2,FALSE)

A4 is the data which im doing the Vlookup... its present in sheet1 at F and i need the column value G... so the column index is 2 and i keep match exact case as FALSE...

so wat i need to know is, i need to iterate through each value in column A and get the value at B...

can i write something like this...?

for i = 1 to lastrow_in_col_A
cells(i+3,"B").value = Vlookup('A' & i + 3 ,'sheet1'!F:G,2,FALSE)
next i

is it possible for me to loop through every value in column A? tat is wat i need to know...
appreciate ur help...

 

[Scott Huish]

You should be able to do that without a loop:

Sub test()
Dim lastrow_in_Col_A As Long
lastrow_in_Col_A = Range("A" & Rows.Count).End(xlUp).Row + 3
With Range("B4:B" & lastrow_in_Col_A)
    .Formula = "=VLOOKUP(A4,'sheet1'!F:G,2,FALSE)"
    .Value = .Value
End With
End Sub

If you actually want the formula in the cell instead of the result:

Sub test()
Dim lastrow_in_Col_A As Long
lastrow_in_Col_A = Range("A" & Rows.Count).End(xlUp).Row + 3
Range("B4:B" & lastrow_in_Col_A).Formula = "=VLOOKUP(A4,'sheet1'!F:G,2,FALSE)"
End Sub

 

[macro_user]

gr8... let me try this... I'm amazed at the speed of the replies at this forum... its really good... you guys are doing a great job...
Keep it going...