Use the Excel Vlookup Function in VBA

Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )


VBA Vlookup Example

Spreadsheet of Employee Hourly Pay Rates Used in VBA Vlookup Example
The following simple VBA function receives an employee name and a range of cells that contain rates of pay, as shown in the example spreadsheet on the right.
The VBA code calls the Worksheet Vlookup function, and uses this the look up the rate of pay for the supplied employee name.
For example, if the supplied employee name was "Benson, Paul" and the supplied Hourly_Rates range was columns A-B of the spreadsheet on the right, the VBA Vlookup would return the value $32.00.

' Function to look up the rate of pay for a supplied employee name and a supplied range of cells
' containing hourly pay rates
Function Pay_Rate( Employee_Name As String, Hourly_Rates As Range ) As Currency

    ' Call the Vlookup function to look up the rate of pay for the supplied employee name
    Pay_Rate = Application.WorksheetFunction.VLookup( Employee_Name, Hourly_Rates, 2, False )

End Function

Comments

Post a Comment

jeetexceltips@gmail.com

Popular posts from this blog

50 Excel VBA Oral Interview Questions

Ques 01. What is the difference between ByVal and ByRef and which is default ? Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure. ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained. ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference. Ques 02. What is the meaning of Option Explicit and Option Base? Solution: Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0. Option Base - When an array is declared by d
Read more