Excel – application.worksheetfunction.vlookup in vba does not loop

excel, vba

I have got the following code. The code is working only for 1 cell, so I think that the looping process does not occur. Can anyone help me figuring out how I can make the loop work?

Sub test4()Dim i As LongDim lr As Longlr = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row    For i = 1 To lr        Sheets("sheet2").Range("b1") = Application.WorksheetFunction.vlookup(Sheets("sheet2").Range("A1"), Sheets("sheet1").Range("A1:g10"), 2, False)    Next iEnd Sub

Best Solution

I'm assuming (by logics of your code) you mean to advance Column A and Column B by the row number in the For loop).

To my preference (only), I declare and set "sheet2" to a variable, and later on use the With Sht2, makes the code cleaner to read, and mught eliminate possible errors.

Sub test4()Dim i As LongDim lr As LongDim Sht2    As Worksheet' is it "sheet2" or "Sheet2"Set Sht2 = ThisWorkbook.Sheets("sheet2")' finding last row is Column "A"lr = Sht2.Cells(Sht2.Rows.Count, "A").End(xlUp).RowWith Sht2    For i = 1 To lr        .Range("B" & i) = Application.WorksheetFunction.VLookup(.Range("A" & i), Sheets("sheet1").Range("A1:G10"), 2, False)    Next iEnd WithEnd Sub