How to use VLookUp function in VBA Excel

Last Updated on January 11, 2023 by token

1. VBA VLookUp – How to match rows?

Do you want to match Excel records in VBA? VBA in Excel gives you the possibility to use the sheet functions. VBA VLookup is very important spreadsheet function. To use vLookUp function, refer to the worksheet functions using command Application.WorksheetFunction.VLookup. Let’s create two simple tables in Excel by the following pattern. Tables will be used in the example.

Table 1: Paste it into range A1:B7.

1 United States
2 England
3 Germany
4 Japan
5 Canada
6 Austria

Table 2: Paste it into range F1:G4.


Ok, now we need to fill G column using identifier listed in the Id Columns.

Excel VBA How To - VBA Vlookup
Excel VBA How To – VBA Vlookup

First of all, we should create a simple script that fills the cell “E2” with values searched using the function. In the Excel spreadsheet, we would insert the following formula into the cell “E2”: =VLOOKUP(F2,A:B,2,FALSE). In the VisualBasic editor we need to insert a new module. The module should contain the following code using the sheet function:

Sub vLookupExample1()
Cells(2, 7) = Application.WorksheetFunction.VLookup(Cells(2, 6), Range("A:B"), 2, 0)
End Sub

2. VBA VLookUp – How to drag the formula?

Now the only thing we should do is loop our code to fill all the table values:

Sub vLookupExample2()

Dim lngCounter As Long
lngCounter = 2

Do While lngCounter < Application.WorksheetFunction.CountA(Range("F:F")) + 1
Cells(lngCounter, 7) = Application.WorksheetFunction.VLookup(Cells(lngCounter, 6), Range("A:B"), 2, 0)
lngCounter = lngCounter + 1

End Sub
Excel VBA How To - VLookup VBA Function
Excel VBA How To – VLookup VBA Function

Using the CountA sheet function, we checked how many non-empty values ​​are in the “D” column. Loop will be repeated as many times as we have non-empty values in the “D” column. The filling starts with line no. 2, because we have to skip the table headers. In this way, our example using the VLookUp in VBA has been resolved. If you would like to serach values horizontal use the hLookUp function similarly .

If you are looking for similar examples of the practical programming in VBA, I invite you to visit this section: Excel VBA How To. Below the file with an example:


Leave a comment

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

3 thoughts on “How to use VLookUp function in VBA Excel”

%d bloggers like this: