VBA: Multiple VLookUp Results

Question

I need a little help with some code.

I am trying to execute VLookup and has the data displayed in columns O, P and Q.

What I'm trying to do is a column with the letter designation loop ("Global"), starting from row 3 to the last row used. It should match the data in the sheet (“Details”) in column A, starting from row 2.

So, when he finds a suitable value, he will display the results from Details C2 in the Global O2, Details I2 in the Global P2 and Details G2 in the Global Q2.

Then you need to perform a cyclic matching and global matching of all data. If no match is found, display "NA!".

The last thing I need is to delete all rows in Global where no match is found.

The code that I have below does what I need, the only problem is that it is incredibly slow, in minutes, to take 800 lines, and sometimes even more!

Is there any other way to do this that will work more smoothly and faster?

Any help is appreciated!

thanks

`Private Sub btnVlookUp_Click() Dim i, j, lastG, lastD As Long ' find last row lastG = Sheets("Global").Cells(Rows.Count, "B").End(xlUp).Row lastD = Sheets("Details").Cells(Rows.Count, "A").End(xlUp).Row ' loop over values in "Global" For i = 3 To lastG lookupVal = Sheets("Global").Cells(i, "B") ' value to find ' loop over values in "details" For j = 2 To lastD currVal = Sheets("Details").Cells(j, "A") If lookupVal = currVal Then Sheets("Global").Cells(i, "O") = Sheets("Details").Cells(j, "C") Sheets("Global").Cells(i, "P") = Sheets("Details").Cells(j, "I") Sheets("Global").Cells(i, "Q") = Sheets("Details").Cells(j, "G") ' mark the row Sheets("Details").Cells(j, "Z") = "marked" End If Next j Next i ' loop over rows in "details" and delete rows which have not been marked For j = 2 To lastD If Sheets("Details").Cells(j, "Z") <> "marked" Then ' delete unmarked rows Sheets("Details").Cells(j, "A").EntireRow.Delete If Sheets("Details").Cells(j, "B") <> "" Then j = j - 1 ' revert iterator so it doesn't skip rows End If Else: ' remove the mark Sheets("Details").Cells(j, "Z") = "" End If Next j End Sub` 

Answer 1

With the advice here and lots of trial and error, I was able to tweak my code.

I tested this on over 600 entries and it runs in seconds, where the previous code would take minutes.

If you see a better way to make the code below, then let me know, I'm still learning VBA, so all help could be better for me!

Thanks for the support!!!!!!!!

 Private Sub btnVlookUp_Click() Dim i, j, lastG, lastD As Long With Application .ScreenUpdating = False .EnableEvents = False .CutCopyMode = False End With ' find last row lastG = Sheets("Global").Cells(Rows.Count, "B").End(xlUp).Row lastD = Sheets("Details").Cells(Rows.Count, "A").End(xlUp).Row ' loop over values in "Global" For i = 2 To lastG lookupVal = Sheets("Global").Cells(i, "B") ' value to find ' loop over values in "details" For j = 2 To lastD currVal = Sheets("Details").Cells(j, "A") If lookupVal = currVal Then Sheets("Global").Cells(i, "O") = Sheets("Details").Cells(j, "C") Sheets("Global").Cells(i, "P") = Sheets("Details").Cells(j, "I") Sheets("Global").Cells(i, "Q") = Sheets("Details").Cells(j, "G") ' mark the row Sheets("Details").Cells(j, "Z") = "marked" Sheets("Details").Cells(1, "Z") = "marked" Exit For End If Next j Next i On Error Resume Next Sheets("Details").Columns("Z").SpecialCells(xlBlanks).EntireRow.Delete Sheets("Details").Columns("Z").ClearContents With Application .ScreenUpdating = True .EnableEvents = True .CutCopyMode = True End With End Sub 

Answer 2

Your code is very inefficient as it is written forever. You did not specify specifically how many lines are in your “global” and “detailed” sheets (you mentioned 800, not sure if both). But if there were 1000 in each of them, your two loops are 1000x1000 = 1 million cycles.

The best solution is not to use VBA in general, but to use the VLOOKUP function in Excel. Here is what you need to do:

Sorting the information sheet by column A Then in the global sheet, in cell O3, you put the following formula: = VLOOKUP (A3, Details! $ A2: $ I (regardless of the last row), 3, FALSE)

If you are not familiar with this function, it takes the first argument, looks at it in the first column of the second argument until it finds a match, and then returns the value in that row in the third argument column. The last “FALSE” only gives you an exact match, otherwise you will get #NA (if you use TRUE, you will get the closest match).

Then copy this formula all over the sheet.

Then copy the column and paste the values. This eliminates the forum and simply leaves the values, which makes everything much faster.

Then sort the table by this column, and all #NA will fall together, and you can delete it all in one operation.

If you want to do this through VBA, the steps described above can be easily encoded:

 Private Sub btnVlookUp_Click() Dim i, j, lastG, lastD As Long Dim DetailsTable as Range ' find last row lastG = Sheets("Global").Cells(Rows.Count, "B").End(xlUp).Row lastD = Sheets("Details").Cells(Rows.Count, "A").End(xlUp).Row ' Make sure this is sorted. If not, you'll need to add a sort command Set DetailsTable=Sheets("Details").Range(Sheets("Details").Cells(1, 2), Sheets.Cells(lastD, 9)) Sheets("Global").Range("O3")="=VLOOKUP(A3," & DetailsTable.address(external:=true) & "3,FALSE)" Sheets("Global").Range("O3").copy destination:=Sheets("Global").Range( Sheets("Global").cells(3,"O"),Sheets("Global").cells(lastG,"O")) End Sub 

This is the beginning, but you need to go. Good luck

Answer 3

There are a few things you can do to speed up your code easily.

Firstly, if you add the line Application.ScreenUpdating = False at the beginning of your code, this will stop Excel from having to perform all the flickers and flashes that you see during the execution of the code (which actually adds these values ​​one by one, deleting lines, etc. .d., which take a lot of time).

You can then add Exit For at the end of your If statement (right in front of your End If ). This will stop the For Loop nested loop to prevent all data from going through when you have already found what you are looking for.

Finally, I know that you are using j = j - 1 so that your iterator does not skip lines, but it is better to use the opposite direction instead. If you change For Loop to read For j = lastD to 2 Step -1 , this will make Loop work in reverse order, so the deleted lines will not be a problem, and you can delete the "reset" line (this will barely speed up your code, it's just more on how to deal with this common problem).