• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Faster way to vlookup in vba

Hi,

I am using Application.WorksheetFunction.Vlookup in my code. It is taking so much of time(around 4 minutes) to do almost 10 to 12 vlookups for around 6000 rows.

Can you please suggest me a faster way to do vlookup in VBA or any other alternative to vlookup in vba.

Thanks in Advance

Regards,
Dileep
 
hi,

below is the part of the code I used

'Vlookup from Lookup_file
Workbooks.Open OpenPath & Lookup_file
Workbooks(Lookup_file).Worksheets(1).Activate
RM_col = ActiveSheet.Rows(1).Find("TALENTPROFESSIONAL", lookat:=xlWhole).Column
PMO_col = ActiveSheet.Rows(1).Find("PRIMARYMKTOFFERING", lookat:=xlWhole).Column
P_Ind_col = ActiveSheet.Rows(1).Find("PRIMARY_INDUSTRY", lookat:=xlWhole).Column
P_sec = ActiveSheet.Rows(1).Find("PRIMARY_SECTOR", lookat:=xlWhole).Column
SubMO_col = ActiveSheet.Rows(1).Find("SUBMARKET_OFFERING1", lookat:=xlWhole).Column
PEP_col = ActiveSheet.Rows(1).Find("PEP", lookat:=xlWhole).Column
PTR_col = ActiveSheet.Rows(1).Find("PTRCHAMPION", lookat:=xlWhole).Column
Strtdt_col = ActiveSheet.Rows(1).Find("STARTDATE", lookat:=xlWhole).Column
Enddt_col = ActiveSheet.Rows(1).Find("ENDDATE", lookat:=xlWhole).Column


Workbooks(temp).Worksheets(1).Activate

MsgBox "Total time took: " & Format((Timer - strt_time) / 60, "#0.00") & " Minutes or " & Format(Timer - strt_time, "#0.00") & " Seconds."
strt_time = Timer

i = 0
For i = 2 To Lastrow

On Error Resume Next
Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), RM_col - 1, False)
Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), PMO_col - 1, False)
Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), P_Ind_col - 1, False)
Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), P_sec - 1, False)
Cells(i, 9) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), SubMO_col - 1, False)
Cells(i, 10) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), PEP_col - 1, False)
Cells(i, 11) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), PTR_col - 1, False)
Cells(i, 12) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), Strtdt_col - 1, False)
Cells(i, 13) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), Enddt_col - 1, False)

Next i

Workbooks(Lookup_file).Close savechanges:=False


Regards,
Dileep
 
Hi Dileep ,

Using an array to store the relevant lookup data range may help ; avoid using entire columns.

Can you upload your workbook ?

When you lookup Cells(i,1) , instead of using this 9 times as the first parameter of the VLOOKUP function , store the value in a variable , and use this variable as the first parameter.

Instead of subtracting 1 from the column number within the loop , do this once in the .FIND statement itself.

These are simple changes , and may or may not make a big difference , but making them is certainly not going to increase execution time.

Narayan
 
THe problem here is that you are doing 54,000 VLOOKUPS one at a time within VBA using Application.WorksheetFunction - which is very slow - and then populating the results one cell at a time into Excel.

It takes Excel quite a bit of time to move data between VBA and Excel, and because of this you should NEVER call the Application.WorksheetFunction approach from VBA thousands of times when you can instead just use the VLOOKUP function directly in the sheet itself.

So instead, put actual VLOOKUP formulas within Excel - this will takes seconds to process, and not minutes. Either manually put VLOOKUPS in there, or add the VLOOKUP formula to the destination range with something like this:
Cells(2,5).FormulaR1C1 = "=VLookup(A1, B:BB, " & RM_col - 1 & ", False)"
Cells(2,5).AutoFill Destination:=Range(Cells(2,5),Cells(2,Lastrow))

...once for each of the VLOOKUPs you want to perform.

And as Narayan says, you can make this still more efficient if you:
  1. Don't use whole column references, but instead just refer to the cells where there is data. If you have EXcel 2007 or later, using Excel Tables is the easiest way to achieve this.
  2. use MATCH to actually work out the row where the thing you're looking up is, and then reuse that row number to retrieve all the associated entries from multiple columns
  3. Upload a sample workbook, so we can help you implement this. We don't need lots of data...just a sample of a few dozen rows. And you can change any confidential data to dummy data.
 
Hi Dileep

I imagine your columns are coming in at a different place each period which is why you are needing to trap them individually? Otherwise the code can bee made simpler. A file is very helpful when posting as we can see the data structure.

I have managed to moch up your file and on my laptop I can process 54,000 vlookups in 2.1 seconds. The key is to perform the Vlookup calcs in memory. In VB, Excel does not mind if you use the whole column reference for Vlookup formulas.

However as the others have mentioned if given a choice between native Excel and VB always go with the simpler of the two, everyone would prefer to debug formula than VBA. :)

Here is the code and file. You will notice it is all in the same workbook for simplicity. I would copy the sheet you need each period into this workbook and run it from here as part of your process.

Code:
Option Explicit
 
Sub LookmeUp()
Dim i As Integer
Dim j As Integer
Dim lr As Long
Dim var As Variant
Dim ar As Variant
Dim t As Double
t = Timer
 
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
ReDim var(1 To lr, 1 To 9) 'Change 9 to suit
ReDim ar(1 To 9, 1 To 1) 'Change 9 to suit
 
    For j = 1 To UBound(ar) 'Trap the position of the column
       ar(j, 1) = Sheet1.Rows(1).Find(Sheet3.Range("A" & j + 1), , , xlWhole).Column
    Next j
  
    For i = 2 To lr 'Trap the values of the Vlookup
        For j = 1 To UBound(ar)
            var(i - 1, j) = Application.VLookup(Cells(i, 1), Sheet1.[A:J], ar(j, 1), 0)
        Next j
    Next i
    Sheet2.Range("B2:J" & lr) = var
MsgBox Timer - t
End Sub

File attached to show workings.

Take care

Smallman
 

Attachments

  • Lookup.xlsm
    61.1 KB · Views: 324
Respected Sir,
Is this possible return value would with last match(Like--Lokkup(2,1/search) with this code ????
 
Hi I run the Code. Its working in your file. However the same code is hitting error 91 (Object Variable or With Block Variable not Set)
I hit the error when come to below statement. Kindly help.

ar(j, 1) = Sheet1.Rows(1).Find(Sheet3.Range("A" & j + 1), , , xlWhole).Column
 
I'm guessing you don't have Sheet3 (note that this is not a sheet name but sheet code name) in your workbook.

But I'd suggest uploading your workbook where you encounter error to get better help.
 
Back
Top