It runs pretty quick, but when I try it on my file with 23k+ rows, it takes over a minute to run.
Code: Select all
Sub ATest()
Dim i As Long
Dim lr As Long: lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Dim Res As String
Dim t1 As Single: t1 = Timer
Application.ScreenUpdating = False
For i = 2 To lr
On Error Resume Next
Err.Clear
Res = Application.WorksheetFunction.VLookup(ActiveSheet.Range("A" & i), ActiveSheet.Range("C1:D13"), 2, False)
If Err.Number = 0 Then
ActiveSheet.Range("A" & i) = Res
End If
Next i
Debug.Print "#1 " & Format(Timer - t1, "0.000") & " secs"
Application.ScreenUpdating = True
End Sub