Excel VBA Vlookup taking huge time

Asked Aug 01 '17 at 18:59

Active Aug 02 '17 at 17:17

Viewed 161 times

Is there any way to reduce the time that a vlookup takes in vba? I am simply using the .range.formula and vlookup formula for it ..it is taking huge time and getting hung.

Range("BO2:BO" & NewLastRow).Formula = _
    "=VLOOKUP(BN2,'" & OldSheet.Name & "'!$BL$2:$BM$" & OldLastRow & ",2,0)"

edited Jul 09 '18 at 19:34

Community

asked Aug 01 '17 at 18:59

Shashank

2

Help us to help you; **post your current code.** – Gary's Student Aug 01 '17 at 19:01
.Range("BO2:BO" & NewLastRow).Formula = "=VLOOKUP(BN2,'" & OldSheet.Name & "'!$BL$2:$BM$" & OldLastRow & ",2,0)" – Shashank Aug 01 '17 at 19:32
I have more than 300k rows of data for vlookup. – Shashank Aug 01 '17 at 19:32
More code, *added to your question* (not in a comment) would be useful. What are typical values for NewLastRow, OldLastRow ? – Tim Williams Aug 01 '17 at 19:36
Around 300K in each sheet – Shashank Aug 01 '17 at 19:49
1

What have you tried? https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup – OpiesDad Aug 01 '17 at 20:04
1

Possible duplicate of [How to optimize vlookup for high search count ? (alternatives to VLOOKUP)](https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup) – ChrisM Aug 04 '17 at 22:37

Excel VBA Vlookup taking huge time

0 Answers0