Vlookup faster way in vba

[Pavitra Srinivasan]

Hi Dear,

I'm Beginner in Macro. Excel,
I have a large data like (5.6L & 8.6L) i want to do VLOOKUP.
It is taking more than 10 Hurs. immediately excel go to not responding mode.
please give me best solution and aslo plz refer my code attached for your reference.

>>> use code - tags <<<

wsTemp.Activate
         lr = wsTemp.Cells(Rows.Count, 1).End(xlUp).Row
         lc = wsTemp.Cells(1, Columns.Count).End(xlToLeft).Column
            For j = 2 To lrow
               On Error Resume Next
               Lookup = wsDump.Cells(j, 7).Value
               wsDump.Cells(j, 20).Formula = Application.WorksheetFunction.VLookup(Lookup, Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, lc)), 2, 0)
               wsDump.Cells(j, 21).Formula = Application.WorksheetFunction.VLookup(Lookup, Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, lc)), 3, 0)
               wsDump.Cells(j, 22).Formula = Application.WorksheetFunction.VLookup(Lookup, Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, lc)), 4, 0)
               wsDump.Cells(j, 23).Formula = Application.WorksheetFunction.VLookup(Lookup, Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, lc)), 5, 0)
            Next
            On Error GoTo 0: On Error GoTo -1
           Application.CutCopyMode = False

plz repely as soon as possible.

I'm waiting for your answer.

 

[BobBridges]

I have a few ideas.

1) You're putting the last row in lr, and then doing the loop from 2 to lrow. I assume you mean to do the loop to lr, not to lrow. (Or maybe not, but you're not saying what lrow is; I just assumed.)

2) You're repeating "Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, lc))" four times inside the loop, and repeating that for every row in the searched range. I don't know much about how VBA works internally, but maybe it's recalculating that range every time it sees it, so if you have 60 000 rows it's figuring out what that range is 240 000 times. There's no need for that, since that range remains the same throughout the loop. Better, I suspect, to do this:

lr = wsTemp.Cells(Rows.Count, 1).End(xlUp).Row
lc = wsTemp.Cells(1, Columns.Count).End(xlToLeft).Column
Set org = Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, lc))
For j = 2 To lr
  Lookup = wsDump.Cells(j, 7).Value
  wsDump.Cells(j, 20).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 2, 0)
  wsDump.Cells(j, 21).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 3, 0)
  wsDump.Cells(j, 22).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 4, 0)
  wsDump.Cells(j, 23).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 5, 0)
  Next

I don't know, but maybe that'll speed it up.

3) Actually I don't see that you need to take in all those columns in the wsTemp range; you're only using cols 1 through 5.

Set org = Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, 5)) 'you can skip finding lc, too

4) Here's a big one: Every time you put something new into a cell, Excel has to adjust the display and check for any formulae that it might have to recalculate. I suspect you'll speed this up enormously if you turn off those features at the start of the program and restore them only after everything has changed, like this:

lr = wsTemp.Cells(Rows.Count, 1).End(xlUp).Row
Set org = Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, 5))
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For j = 2 To lr
  Lookup = wsDump.Cells(j, 7).Value
  wsDump.Cells(j, 20).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 2, 0)
  wsDump.Cells(j, 21).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 3, 0)
  wsDump.Cells(j, 22).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 4, 0)
  wsDump.Cells(j, 23).Formula = Application.WorksheetFunction.VLookup(Lookup, org, 5, 0)
  Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

5) There's something odd here: I assumed at first that you were doing the lookup in the program (using Application.WorksheetFunction.VLookup) and putting the resulting value into wsDump. Instead, you're putting it into the Formula property of the cells in wsDump. You say this ran 10 hours; did you ever let it complete, and look at the results? I'm having a hard time picturing what would come out. What values will the VLookup find in wsTemp? Are they numeric, or string, or what?

This actually gets into another idea I have about speeding it up, but let's take an example. Let's say that the VLookup finds the value "42" in wsTemp. I think what would happen by giving "42" to the Formula property is that instead of putting the value 42 into the target cell, it would put "=42". But I'm not sure. If you really want it to be a formula, you could change it to something like this:

wsDump.Cells(j, 20).Formula = "=VLOOKUP("Temp!F" & jr & ",Temp!A2:E" & lr & ",2,0)"

That results in wsDump having formulae like "VLOOKUP(Temp!F2,Temp!A2:E60000,2,0)".

6) Which brings me to another question: Why have a program do it at all? Just put the VLOOKUP formula in wsDump!V2 through Ylrow and let Excel do the VLookup. Two reasons this'd probably be faster, in fact much faster: a) Excel is pretty fast anyway; VBA is comparatively slow. And b) when you change values in wsTemp, you have to run this program all over again—but if you put the formulae in wsTemp, instead of doing the program, Excel will do the VLookup only for the cells that actually changed. Excel is very smart about that.

7) However, if for some reason you still need to have the program do this for every row, every time, forget putting the results in wsDump(j, 20).Formula and put them in the Value property instead. But here's the trick: Instead of putting the values into wsDump one cell at a time, put them into an array and then dump the array into wsDump. That takes more programming work but it's blindingly fast. Like this:

Dim ar()
lr = wsTemp.Cells(Rows.Count, 1).End(xlUp).Row
ReDim ar(2 to lr, 20 to 23)
Set org = Range(wsTemp.Cells(2, 1), wsTemp.Cells(lr, 5))
For j = 2 To lr
  Lookup = wsDump.Cells(j, 7).Value
  ar(j, 20) = Application.WorksheetFunction.VLookup(Lookup, org, 2, 0)
  ar(j, 21) = Application.WorksheetFunction.VLookup(Lookup, org, 3, 0)
  ar(j, 22) = Application.WorksheetFunction.VLookup(Lookup, org, 4, 0)
  ar(j, 23) = Application.WorksheetFunction.VLookup(Lookup, org, 5, 0)
  Next
Range(wsDump.Cells(2, 20), wsDump.Cells(lr, 23)).Value = ar

I haven't tested any of this code, so there may be bugs or typos in it. But those are my ideas.

Oh, one minor one: 8) I did some testing and discovered that it takes VBA about ten times as long to look up the property of an object than to have it directly. So I've gotten into the habit, right after assigning the worksheet objects to wsTemp and wsDump, to assign their Cells to other variables, like this:

Set csTemp = wsTemp.Cells
Set csDump = wsDump.Cells
Set org = Range(csTemp(2, 1), csTemp(lr, 5))
' And later, inside the loop:
  Lookup = csDump(j, 7).Value

Compared to items #4 and #7 this is a minor thought, but it may help a little.