Office tutorials

How to use VLookUp function in VBA Excel

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.

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

Table 2: Paste it into range F1:G4.

ID VALUE
1
4
6

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:

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:

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:

excel_vba_vlookup.xlsm

2 comments

    • ser on May 27, 2018 at 9:30 pm

    Reply

    I must say got into this post. I found it to be interesting and loaded with unique points of interest.

    • ado on June 12, 2018 at 3:36 am

    Reply

    This i like. Thanks!

Leave a Reply

Your email address will not be published.