VBA VLookup Performance

[kbashaw]

Hi there! Does anyone have a suggestion about improving the performance of this lookup? The code works but it take 9 minutes to run through 47,000 records. I technically don't need the message error running but I couldn't figure out how to get it to run without it. Is there another way I can run this code faster? Thanks in advance.

Sub VLookupStep1()
Dim Sh1, Sh2 As Worksheet
Dim MyRange As Range

Set Sh1 = ThisWorkbook.Sheets("Step1")
Set Sh2 = ThisWorkbook.Sheets("P21")
Set MyRange = Sh2.Range("B:C")

LastRow = Sh1.Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
On Error GoTo Error:
Sh1.Range("D" & i).Value = Application.WorksheetFunction.VLookup(Sh1.Range("C" & i), MyRange, 2, False)
Next i
Error:
Sh1.Range("D" & i).Value = "N/A"
Resume Next
End Sub

 

[Tupe77]

Behind the link there is a thread with the lookup function I made. However, without sample data it is difficult to assess its suitability for your problem.

VBA code to lookup multiple columns using Dictionary

VBA code to lookup multiple columns using Dictionary. Image of data attached. Thanks

www.mrexcel.com

The operation requires that the information being searched is unique and the search match is exact.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA VLookup Performance

Hi there! Does anyone have a suggestion about improving the performance of this lookup? The code works but it take 9 minutes to run through 47,000 records. I technically don't need the message error running but I couldn't figure out how to get it to run without it. Is there another way I can run...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.