The macro code can eliminate repetitive, boring tasks, and automate the process. Once the code is written in VBE, you can execute the same task at any time in the workbook. Source: VBA Tutorial () What are the Macros in Excel?Ī macro is nothing but a line of code to instruct the excel to do a specific task.
#ADVANCED EXCEL VBA TUTORIAL PDF HOW TO#
You are free to use this image on your website, templates etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked Step 3: Click on OK to Start the Recording.Step 3:Check the box DEVELOPER to enable it.Step 1: Go to FILE -> Under FILE, go to OPTIONS.If you want to specify an absolute range, you need to specify the R1C1 cells without brackets e.g. One thing that is worth noting here is: the square brackets ( ) in your R1C1 formula indicate that you are specifying a relative range. So, the formula =VLOOKUP(RC, R3C8:R13C9, 2, False) means =VLOOKUP(,, , ). In this code, we have applied the VLOOKUP in R1C1 form. Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC, R3C8:R13C9, 2, False)"Įxplanation: This code is very similar to the one that we have discussed in Example 3, the only difference between these formulas is that here we are copying the VLookUp formula directly in the cells. Redo example 3 but this time paste the whole VLookUp formula instead of pasting only the result. MsgBox "You have entered an invalid value."Įxplanation: In this example, we have asked the user to enter the Employee Id and then we have used multiple VLookUp Statements and concatenated their outputs to show all the details in a single message box. MsgBox "Employee Details : " & vbNewLine & Det 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. E_name = InputBox( "Enter the Employee Name :")
0 kommentar(er)
