• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

How do I perform a VLOOKUP in Excel VBA ?

Hi All,

I've been trying to use a VLOOKUP in Excel VBA but keep returning an error against the lookup range. The error screenshot is attached.

I have two sheets - 'Active Directory' and 'K8'. I'm looping through column V:V in sheet Active Directory! and populating the blank cells with the returned values from a lookup on sheet K8!.

The lookup value I am using is in column A of sheet Active Directory!. The lookup range is in sheet K8! B9:Ix (where x is the bottommost row").

My code is below. At runtime it falls over on the 'LookupRange =  wsK8......' line.
Any help appreciated.


Public Sub Populate_EmployerID_From_K8()

    Dim wsAD As Worksheet
    Dim wsK8 As Worksheet
    Dim LookupRange() As Variant
    Dim i As Long
    Set wsAD = ThisWorkbook.Sheets("Active Directory")
    Set wsK8 = ThisWorkbook.Sheets("K8")
    LookupRange = wsK8.Range(Cells(9, 2), Cells(1048576, 9).End(xlUp)).Value
    For i = 9 To 5000
' Load the array from sheet "K8"       
       If wsAD.Range("V" & i).Value = "" Then
          wsAD.Range("V" & i).Value = Application.VLookup(wsAD.Range("A" & i).Value, LookupRange, 9, False)
       End If
    MsgBox ("Your process has completed successfully")

End Sub

Open in new window

  • 2
2 Solutions
Rory ArchibaldCommented:
You haven't qualified your Cells calls:

LookupRange = wsK8.Range(wsK8.Cells(9, 2), wsK8.Cells(1048576, 9).End(xlUp)).Value

Open in new window

TocogroupAuthor Commented:
Oh ! Such a basic mistake.
Thanks for that.

I tried this macro out on the first couple of blank cells of AD! and it returned a #N/A (value not found) which is fine.

However the third occurrence of the loop should have returned a value, but returned a #REF! This happens when the lookup tries to reference an empty cell isn't it. The lookup value (AD! A11) certainly has a valid value in it. Any ideas as to where I should look?

Your range (LookupRange) is started from column 2, so column in:
Application.VLookup(wsAD.Range("A" & i).Value, LookupRange, 9, False)
should be 8:
Application.VLookup(wsAD.Range("A" & i).Value, LookupRange, 8, False)
TocogroupAuthor Commented:
Ahhh! Another basic mistake.
Thanks to you both. Much appreciate your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now