Using the VLookup Function in VBA
The VLookup function is an Excel function. It can be used directly on your worksheet without having to use any VBA code. However, there may be instances in VBA where you wish to also use VLookup functionality.
You would then need to call the VLookup function from within your VBA code. Excel functions can be used within VBA code, but have to be used in a specific way.
To use an Excel function in VBA you have to use the Application.WorksheetFunction
method.
This method gives you access to all the built-in functions that are available in Excel – VLOOKUP being one of them.
Contents
VLookup Syntax
The syntax is as follows:
Result = Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup])
Using VLookup to look up a value
Consider the following sheet:
Using the example above, we want to look up the Average Monthly Balance for a certain account number.
Sub TestLookup() Dim strN As String strN = Application.WorksheetFunction.VLookup(568, Range("A5:H30"), 8, False)
The procedure above would look up account number 568 and return the Average Monthly Balance for that account.
Using VLookup with an InputBox
This type of lookup could be incorporated into a macro that interacts with the user. Instead of hard-coding the account number to lookup, the macro could ask the user to input the account number, and then return the available balance.
Sub LookupBalance() Dim curBal As Currency Dim intA As Integer intA = InputBox("Please enter the account number") curBal = Application.WorksheetFunction.VLookup(intA, Range("A5:H30"), 8, False) MsgBox "The current balance is " & curBal End Sub
When you run the procedure, the following input box will appear
Type in 646 and click OK.
Populating a list in a separate sheet using VLookup
There may be an occasion when you need Excel to loop through your code and look up values and then pass those values to a separate sheet.
For example, using our list of bank accounts, we may have another Excel sheet with the account numbers listed in them, and we wish to look up the names of the customers against each account number.
Sub FindNames() On Error GoTo eh 'declare the variables Dim intAccount As Integer Dim strFirstName As String Dim strSurname As String Dim i As Integer 'select the first account in sheet 2 Sheet2.Range("A2").Select 'loop for 26 rows For i = 2 To 27 intAccount = ActiveCell 'lookup the first name strFirstName = Application.WorksheetFunction.VLookup(intAccount, Sheet1.Range("A5:H30"), 3, False) 'lookup the surname strSurname = Application.WorksheetFunction.VLookup(intAccount, Sheet1.Range("A5:H30"), 4, False) 'populate the active cell ActiveCell.Offset(0, 1).Value = strFirstName & " " & strSurname 'loop to the next cell ActiveCell.Offset(1, 0).Select Next i Exit Sub eh: MsgBox Err.Description End Sub
When you run the code, Sheet 2 will be populated as follows:
Things to look out for
You have to pass the correct data type to the VLookup if you are passing a variable.
The code above does not like me passing a string variable to the lookup when the data in Excel is numeric. It throws a Run-time error 1004.
If the lookup cannot find the value, the same error will occur!
A simple error trap will solve the problem:
In this article we have learnt how to use VLookup in VBA. This is a Worksheet function as we have discussed. You are able to use all the built-in Excel functions in VBA by adding the Application.WorksheetFunction method to your VBA code.