Vlookup with VBA - Excel VBA Tutorial By Exceldestination

in this tutorial we will see how we can use vlookup function with VBA code on this sheet I have data from column a to D we have employee ID employee name Department and cell D now I want that using this data I want salary in column AI for the given employee IDs that are in column H so if I use vlookup without VBA code how we can use that we will select vlookup function and this vlookup function requires four arguments first is lookup value so I will select H for table array I select my table that is in column a to D or I will select for columns column index here we can see that cell is 4th column in my table area selection so I will give for range lookup I want the exact match for that I'll use false so these are four arguments we can give in vlookup function and click on ok we can see that cell e4 employee ID 1 0 0 4 is there in column i in cell I 4 I can drag this formula down to get cell your all required employee IDs so this is how we can use vlookup function manually but what I want I want vlookup function to be used by VBA code so here we can see that we have one command button on this sheet when I click this command button then cell is populated for all the given employee IDs so what I have done I have written some VBA code that is using VBA code with vlookup and once I click this command button that code is getting executed so if I go to Developer tab click on design mode then I will double click on this command button and see that this VBA code we can write for using vlookup and once we click command button this code will be executed and we can use vlookup through VBA so in this example let's understand how we can develop this step-by-step so as a first step let me copy this data and paste on a new excel file now let me copy employee ID and salary somewhere else let me increase width of these two columns and now I will go to Developer tab click on insert from ActiveX controls select the command button I will drag a command button here I will double click on this command button and then here we can write VBA code for we look up so let me close the previous file don't save any changes now here I will copy few employee IDs paste here and then let me give order to look it better so on this command button I'll write VBA code that believes vlookup to get Sally for these employee IDs so let me double-click and write VBA code for that so first of all highlight VBA code that we'll use we look up only for Sally of employee ID 1 0 0 5 so we can see that employee ID is given in H 3 and Sally we need in i3 so VBA code for that I am right here is worksheets then sheet name is sheet 1 dot cells row number is 3 column is I that means nine dot value equal to application dot worksheet function dot we look up bracket a star and we know that we look up requires four arguments so if I continue in this line it will not be visible so let me give one a space and underscore then I will press ENTER now I can continue with my VBA code so first parameter is lookup value for that we way could I write worksheets then sheet one that is name of this sheet dot cells formula I want in cell i but lookup value is in H 3 that means column hate children number is 3 and H that means 8 column dot value comma so this VBA code represents first argument of we look up that is lookup value second argument requires table we have data in column a 2d so for that code is worksheets then sheet one that is name of this sheet dot range here in inverted commas I write a to D that is range let me you comma so this becomes second argument that is table array third argument is column index you know that cell is fourth column in table array so I will give four here then false we can represent by 0 and now I will close bracket press ENTER let me minimize this VBA window and go to Developer tab then I will click on design mode to come out of design mode now when I click this command button there's some error let me see what is that error so we have not given sheet 1 here click on reset let me click this command button and we can see cell is there for employee ID 1 0 0 5 but this VBA code is getting selling only for 1 employee ID however we want for all employee IDs so these employee IDs we can see are in row 3 to 9 so I will go to VBA window and I'll use for loop where I can write for I equal to 3 to 9 and here is a next press ENTER it will take formatting automatically and wherever we have used row I will replace that with I sir here and replace these three by I that is the variable that we have used in loop and here also I so now this loop will execute for three to nine that means row number three to nine and we have passed in this cell argument let me minimize this let me delete this now when I click this button we can see that cell is populated for all the employee IDs so let's take a look of VBA code again so what I am done I am used for Lu and then this is the code that we can use we can use the entire code in single line for these two lines and as we know that we will cop takes four parameters so first parameter that is lookup value is this VBA code second is table array that is this VBA code third argument is column index that is fourth in this example and false we can represent by zero so this is how we can use vlookup function with VBA code for any microt requirement or any automation thanks for watching this tutorial if you liked it please subscribe to my channel thank you

Channel: Excel Destination

Views: 54,537

Rating: 4.7049179 out of 5

Keywords: vba vlookup function, vlookup with vba, vlookup using vba, vlookup vba, vba vlookup, vba code for vlookup

Id: SXFJZDBygJE

Length: 10min 53sec (653 seconds)

Published: Sat Dec 09 2017