VBA instead of vlookup

Question

I just started to use the VBA. I have a workbook with two sheets. On the first sheet, in column "D" I have part numbers. On the second sheet, again I have part numbers in column "A", and the description of each part numbers in column "B". On the Sheet1 I want to collect the following data:

If Sheet2 column "A" contains a part number from Sheet1 column "D", then the column "O" value need to be Sheet2 column "B".

Now self using Vlookup through recording macro:

Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],'RIG MB'!C[-14]:C[-13],2,0)"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O3430")
    Range("O2:O3430").Select

I need a vba code for it, but not the vlookup worksheetfunction, I need something faster. Also I want to loop it, until the Sheet1 column "D" has data in it. Thank You in advance!

Hope There is no Possible to bring data without using 'Vlookup()' or any other functions. If it's macro also needs to use the 'Vlookup()' or any other match functions. I have tried your method through recording macro, it's working fine by auto 'Vlookup()' and paste special the same. If you want know the step of recording macro let me know , i will post the same in answer. — Regiz
I recorded the macro, but it's to slow, because I have use in more than 5000 rows. I need a code, what handles the rows until they have value, not an exact range (because I add more rows to Sheet2, I have to update the macro too now — evolution7even
Can you please upload the Query by modifying your question. That you have tried through recording macro. So that, can be understand why it's slow. — Regiz

Excel Developers Excel Developers · Accepted Answer · 2019-11-07T13:06:12

Here is an example that doesn't loop, but should be faster than VLOOKUP:

Range("O2:O3430").Formula = "=INDEX('RIG MB'!B:B, MATCH(D2, 'RIG MB'!A:A, 0))

VBA instead of vlookup

1 Answers