1
votes

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!

1
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
You can use a dictionary, which will be much faster. - Excel Developers

1 Answers

0
votes

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))