![dlookup excel vba examples dlookup excel vba examples](https://www.599cd.com/tips/access/images/dlookup-wpe24.gif)
#Dlookup excel vba examples code
In this example we will try to write a code that displays all the details of an employee from the Employee table (as shown below) when its Employee ID is entered.īelow is the code that can accomplish this:Į_id = InputBox( "Enter the Employee ID :")ĭet = "Employee ID : " & (E_id, Sheet1.Range( "A3:E13"), 1, False)ĭet = Det & vbNewLine & "Employee Name : " & (E_id, Sheet1.Range( "A3:E13"), 2, False)ĭet = Det & vbNewLine & "Employee SSN : " & (E_id, Sheet1.Range( "A3:E13"), 3, False)ĭet = Det & vbNewLine & "Monthly Salary : " & (E_id, Sheet1.Range( "A3:E13"), 4, False)ĭet = Det & vbNewLine & "Department : " & (E_id, Sheet1.Range( "A3:E13"), 5, False) Please note that in this code we have just pasted the result of the VLOOKUP formula, and not the VLookUp formula itself (Refer Example 5). Sheet1.Cells(Dept_Row, Dept_Clm) = (cl, Table2, 2, False)Įxplanation: This code takes each ‘ lookup_value’ from the Employee ID field (one at a time), looks up its corresponding Department, and then populates the corresponding department value at the appropriate place. Table2 = Sheet1.Range( "H3:I13") ' Range of Employee Table 1ĭept_Row = Sheet1.Range( "E3").Row ' Change E3 with the cell from where you need to start populating the Department Table1 = Sheet1.Range( "A3:A13") ' Employee_ID Column from Employee table So, in this case, we will have to apply the VLookUp based on the Employee ID. In this example we will try to write a code that adds the Department field from the Employee Table 1 to our old Employee Table.Īs you can see that in both these tables there is only one common column i.e. However, if the employee name is not present in the table then VLOOKUP throws a “1004 Error”.Īnd, we have created an error handler to catch such cases for conveying the user that entered employee name doesn’t exist. If the Employee name entered by the user is found, then VLookUp returns its corresponding salary. MsgBox "Employee Not Present in the table."Įxplanation: In this code, we are accepting the user input using an InputBox function.
![dlookup excel vba examples dlookup excel vba examples](https://www.599cd.com/tips/access/130724-dlookup-no-programming/defaul7.jpg)
E_name = InputBox( "Enter the Employee Name :")