Replace values in column A with results from a lookup table

[gailb]

I found this code to do a VLOOKUP on column A against a lookup table in C2:D16.

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

Is there a better process to use? I've looked into the scripting dictionary or array, but can't quite understand it.

[HansV]

This is much faster:

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
    Range("B2:B" & lr).Insert Shift:=xlShiftToRight
    With Range("B2:B" & lr)
        ' Lookup range has shifted!
        .Formula = "=IFERROR(VLOOKUP(A2,$D$1:$E$13,2,FALSE),A2)"
        .Value = .Value
    End With
    Range("A2:A" & lr).Delete Shift:=xlShiftToLeft
    Debug.Print "#1 " & Format(Timer - t1, "0.000") & " secs"
    Application.ScreenUpdating = True
End Sub

[gailb]

Wow. That is amazingly faster. I guess it's the simple looping that makes the difference. Thank you.

[HansV]

My version doesn't loop at all - it lets Excel do all the hard lifting.