Month: November 2021

In this Excel VBA tutorial, you will learn how to implement the Vlookup function in VBA code.

I have shown 3 examples of using Vlookup VBA. You will also see how Vlookup can be used in the VBA userform to autofill some of the entries.

Vlookup VBA code syntax

You can use one of the below syntaxes to use Vlookup in macro.

Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index_num, range_lookup)

or

Application.VLOOKUP(lookup_value, table_array, column_index_num, range_lookup)

In the below examples I am using 1st version of Vlookup in macro.

Examples of Vlookup VBA code

Here you will see some examples of using Excel Vlookup VBA.

Excel Vlookup VBA Example – 1

By using the below table, display the message box having first name of the given ID as lookup value.

Let’s see the Vlookup VBA code for this.

In the below example, variable lookupVal  is id no. By Vlooking Id number the below code displays the First name of that id. 

Variable tblRng is 2nd input in VBA Vlookup code. Sheet1 is code name of sheet in which the table is present. 

Suggested reading: How to insert and run VBA code in Excel?

Sub displayName()
  Dim lookupVal As Variant
  Dim tblRng As Range, FstName As String
  lookupVal = 6
  Set tblRng = Sheet1.Range("A1:F12")
  FstName = Application.WorksheetFunction.VLookup(lookupVal, tblRng, 2, False)
  MsgBox "Id No " & lookupVal & " fist name is > " & FstName
End Sub

If you run the the above code then below message box will be displayed for Id number 6.

Handling Error in Excel Vlookup VBA

There may be instances when the lookup value is not found in table_array or table range.

Example:-  In the above when you make lookup value lookupVal = 22 and run the code, “run error 1004” will be thrown since 22 is not present in ID column. 

You can handle this error using the below code.

Sub displayName()
  Dim lookupVal As Variant
  Dim tblRng As Range, FstName As String
  lookupVal = 22
  Set tblRng = Sheet1.Range("A1:F12")
  On Error Resume Next ''this line will prevent run time error for below lines
  FstName = Application.WorksheetFunction.VLookup(lookupVal, tblRng, 2, False)
  If Err.Number = 0 Then
    MsgBox "Id No " & lookupVal & " fist name is > " & FstName
  Else
    MsgBox "Id No " & lookupVal & " is NOT FOUND"
  End If
End Sub

Error handling explanation

Error will be thrown at below line if lookup value is not found.

FstName = Application.WorksheetFunction.VLookup(lookupVal, tblRng, 2, False)

To ignore the error for the above line you can use the below code line so that error can be ignored and the code runs without any break.

On Error Resume Next

The above code will make VBA to ignore all the run time errors for the code lines below it. But behind the seen flag will be raised for error with the error number. 

Now you can know whether is error is raised or not by using Err.Number. Err.Number =0 then means no error is there and if not zero then some type of error is there.

If you run the above code section then the message box will show “Id No 22 is NOT FOUND”, instead of throwing an error.

Excel Vlookup VBA Example – 2

You can use the below code to dynamically vlookup the all information of a given ID.

In the below code I have used the Input box to input and store Id value for Vlookup.

If you run the below code 1st input box will popup, enter Id value in the input box.

After entering the Id value all the information of entered Id is displayed in the message box.

Sub displayIdInformation()
  Dim id As Variant, tblRng As Range
  Dim i As Byte, val As String, str As String
  id = Application.InputBox("Please write correct ID no", Type:=1)
  Set tblRng = Sheet1.Range("A1:F12")
  i = 2
  For i = 2 To tblRng.Columns.Count
    val = Application.WorksheetFunction.VLookup(id, tblRng, i, False)
    If i = 4 Then val = Format(val, "dd-mm-yyyy")
    str = str & vbNewLine & val
  Next i
  MsgBox "Information of ID NO " & id & " is :" & str
End Sub

Excel Vlookup VBA Example – 3

Sometimes you may need to automate writing Vlookup formula in a particular cell or range in Excel sheet

In the below image in range I2 to I5, instead of manually writing the Vlookup formula to bring Blood Group, you want to do it using VBA. 

Below is the macro for Vlookup formula which automatically writes Vlookup formula for Blood group in a range.

Suggested Reading How to Create macro button in Excel

Sub writeVlookupFormula()
  Dim writeRng As Range, lrw As Long
  lrw = Range("H1").End(xlDown).Row
  Set writeRng = Sheet1.Range("I2:I" & lrw)
  writeRng.Formula = "=VLOOKUP(H2,$A$1:$F$12,5,FALSE)"
End Sub

Vlookup in VBA userform

You can use Vlookup to autofill some of the controls like the text box in VBA userform.

In the below GIF you can see after entering the Id value First name and Last name is getting auto-updated or filled.

Below is the VBA code write in userform module

Private Sub TextBox_Id_Change()
 Dim id As Integer, tblRng As Range
 Dim fNm As String, lNm As String
 If TextBox_Id.Value = "" Then
    TextBox_FirstName.Value = ""
    TextBox_LastName.Value = ""
    Exit Sub
 End If
 id = TextBox_Id.Value * 1
 Set tblRng = Sheet1.Range("A1:F12")
 On Error Resume Next
 fNm = Application.WorksheetFunction.VLookup(id, tblRng, 2, False)
 lNm = Application.WorksheetFunction.VLookup(id, tblRng, 3, False)
 On Error GoTo 0
 TextBox_FirstName.Value = fNm
 TextBox_LastName.Value = lNm
End Sub

A quick explanation of working

In the userform 3 text box namely TextBox_Id, TextBox_FirstName,TextBox_LastName are there.

All the code is written Textbox_Id change event, which means whenever the value in Id text box changes code written in it runs automatically.

Here in Private Sub TextBox_Id_Change(), Change is event name TextBox_Id is text box name. 

So if your text box name is TbxId then change event sub will be like below

Private Sub TbxId_Change().

You can see Vlookup is used for fetching First name and Last name for an entered Id value.

Download file Vlookup in VBA Userform

You may also like to read and learn from the below topics

Vlookup to return all matched values in Excel (return multiple matches) using 3 ways

Reverse Vlookup In Excel (Vlookup Right to Left)

Get most from VBA Immediate Window

ByVal And ByRef Difference in VBA With Example