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.

Table 2: Paste it into range F1:G4.

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

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:

'officeinside.org
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:

'officeinside.org
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
Loop

End Sub

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