Vlookup Excel VBA

How can we automate the Vlookup process using Excel VBA?
Before we write the VBA code for Vlookup let’s understand how Vlookup works without VBA. Let’s say we have the data as shown in the image below.

 

Vlookup Using VBA

Vlookup Using VBA

Now we wish to find Noah’s salary quickly. Our Vlookup command in any cell in simple language would look like this:
=Vlookup(Value to be looked up, Data containing the value, column in which the looked up value is located, find exact match)
Using Excel’s formula our Vlookup would look like as shown in the image below:

Vlookup Formula

Vlookup Formula


Vlookup assumes that the value to be looked is always in the first column of data.
The value to be looked can be in any other column 2, 3, 4, etc. The data range containing all the values in our case for example is, A2:B8. Also, we wish to find exactly how much Noah earns. So in the [lookup_range] value we provide the command ‘FALSE’. This can sometimes be confusing for a beginner but that’s how Excel wants you to enter the parameters for a Vlookup.

Before you start working with the Vlookup VBA code watch the training video:


Based on the above explanation our basic VBA code for Vlookup would look like this:

Sub mylookup1()
Dim emp_name As String
Dim salary As Long
emp_name = “Noah”
Set myrange = Range(“A:B”)
salary = Application.WorksheetFunction.VLookup(emp_name, myrange, 2, False)
MsgBox emp_name & “‘s salary is ” & salary
End Sub

To automate the process we can refine the above code where the user inputs the name of the employee whose salary he wishes to display automatically:
Sub mylookup2()
On Error GoTo MyerrorHandler:
Dim emp_name As String
Dim salary As Long
emp_name = InputBox(“Please enter employee’s name”)
If Len(emp_name) > 0 Then
Set myrange = Range(“A:B”)
salary = Application.WorksheetFunction.VLookup(emp_name, myrange, 2, False)
MsgBox emp_name & “‘s salary is ” & salary

Else
MsgBox “You didn’t enter any name!”
End If
Exit Sub

MyerrorHandler:
If Err.Number = 1004 Then
MsgBox “Employee name not in the data!”
End If

End Sub

You will notice that we also provide an error handling mechanism in the VBA code for Vllookup which takes care of 2 facts:

  1. The user forgets to enter an employee name and presses enter
  2. The user enters an employee name that does not exist in our data

The final piece of the Vlookup VBA code provides a way to automate calculations of the salary plus perks that an employee receives:

Sub mylookup3()
Dim lastrow As Long
lastrow = Sheet1.Range(“A” & Rows.Count).End(xlUp).Row
Set myrange = Range(“A:B”)
For i = 2 To lastrow
Cells(i, 3) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 2, False)
Cells(i, 3) = Cells(i, 3) + Cells(i, 3) * 0.3
Next i

End Sub

Further Reading:
VLOOKUP In VBA – With Examples

Power Excel with MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis in Excel 2010–2013

4 thoughts on “Vlookup Excel VBA

  1. w3soulspirit

    Dear sir,
    I have working as loan recovery agent and facing difficulties in followup. So come up with the excel vlookup but still it can show exits data can’t update in exist one direct because its save lot of time can you please help me out problem.

    Reply
  2. Chel

    Hi there! I’m new at VBA and I’d want to ask help for this:
    I have a combobox in a userform which has 2 options (sick leave and vacation leave). If the user indicates vacation leave in the said combobox, the code should compute the number of days between the dates entered in textbox1 (start date) and textbox2 (end date) and should be entered in column E (of the same row with that of the dates entered) of Sheet1.
    Otherwise, if it is sick leave ten the number of days between the dates entered in textbox1 (start date) and textbox2 (end date) and should be entered in column F of the same row and same Sheet.
    The start date and end date should be entered at columns C and D, respectively.

    Reply
  3. Nadine

    on this line of code using Excel 2013 —
    Sub mylookup3()
    Cells(i, 3) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 2, False)”
    I am getting runtime error 1004
    Unable to get the Vlookup property of the WorkSheetFunction class.
    I have copied/pasted from the example on website and still get the error.

    Reply
  4. Pingback: How to use VLookup to get multiple values | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *