Vlookup table error

Question

I am trying to loop the value for column E so that I can use it to VLookup on another worksheet and return the value to column F.

It has been giving me the error

of Application-defined or object-defined error

on the line:

result = Application.WorksheetFunction.VLookup(look, Sheet2.Range("B:H"), 2, 
    False)

My Code

Dim X As Integer
Dim look As Variant
Dim result As Variant
X = 2
Sheet3.Range("E2").Select
Do Until IsEmpty(ActiveCell)
    look = Sheet3.Cells(X, 5).Value
    ActiveCell.Offset(1, 0).Select
    result = Application.WorksheetFunction.VLookup(look, Sheet2.Range("B:H"), 2, False)
    Sheet3.Cells(X, 6).Value = result
    X = X + 1
Loop

Avoid using Select http://stackoverflow.com/q/10714251/4539709- now do you have any values on `"B:H"` — 0m3r, Apr 24 '17 at 06:33

score 0 · Accepted Answer · answered Apr 24 '17 at 06:56

Try the code below (without using Select, and ActiveCell) :

Option Explicit

Sub VLookupHandleNA()

Dim X As Long
Dim Look As Variant
Dim Result As Variant

X = 2
With Sheet3
    Do Until IsEmpty(.Range("E" & X).Value)
        Look = .Range("E" & X).Value
        Result = Application.VLookup(Look, Sheet2.Range("B:H"), 2, False)
        If Not IsError(Result) Then
            .Range("F" & X).Value = Result
        Else ' if Vlookup returns an error, just write something in the cell to let the user know
            .Range("F" & X).Value = "VLookup wasn't able to find " & Look
        End If
        X = X + 1
    Loop
End With

End Sub

Vlookup table error

1 Answers1