VBA For Loop Data Matching using Array

hello and good day welcome to the part 2 of our Excel for loop data matching and and for this video we will focus on how to use away in in matching data so previously we use the normal way of the for loop from to worksheet so now what we're going to do is use an away so first we need to declare two of h1 the purse away will hold our load data it's like a table of a with with with multiple columns and then the second way will hold our search data and by default there should be no value on column that will hold for address city state and traveling so let's get started make sure to open your VBA environment and then let's insert another module let's name it a base for loop and let's create a new procedure have a much data alright so again we'll need to declare our two worksheet first so let's say our sh and ssh as worksheet and then let's set this variable to this workbook that shades o data and set as search data right so we need to declare our two away as well so let's say go away this is how you declare an array let's declare a blank of a poor now then later we'll we'll put the size of each of a so the next one is the search array string right now we need to put a value in this array let's start with the row of a so let's use redeem preserve Robbie is equals to 1/2 so this is devil power of a so this should be equals to the last row of power a power column number a so let's say our stage the trains a and rows that count that end Excel AB the true that will be our row but we need to have here minus 1 because of the header and then for the column we have here we have your 11 column so let's say 1 to 11 in here so now in this line the devil of a will be dimension to this size and then we also need to dimension our search data array so it's like converting this table or sending this table to an array so later you will see how that works and then let's save a Dean reserve surgery start with devil it's 1/2 the ssh at range a and rows that count and Excel app that the throw again minus 1 because there's we don't need to include the header or maybe we should yeah maybe we should let me remove the minus 1 in here and then how many : do we have on search we have here 7 so it's 1 2 7 ok let's try to check this away first so make sure to open your local window so if you don't have this just go to view and look for the local window okay now let's try to run it using debug or f8 from your keyboard so here you can see that after we set the way we have here - with nothing on it so let me now we will dimension the first array which is double array and you can see here that hey what happened I am sorry so you can see you know away that all the rows are now created we have one thousand one go or number of items and here we declare one to eleven so we should see here eleven columns so this represents the row and then this represent the column for each row we have here 11 for each row right and then weave it with I mentioned the search away so you can see here that we have 380 row and then we said it should have 7 column for each row so that's our array and you can notice that it's currently blank same with our lower a so we need to start with our array we need to transfer our load data to this away we need to transfer all the value from that worksheet to this away so how to do that let's go back to our VBA code so here let's create a for loop 1 2 RS a change a and roasted count I should have put it to Powerball but let's continue this and this is the loop border oh so from one to the last row and then this one is for the column RSH it sells one and columns that count the end Excel to lab 4 : that column next me so this loop is forever : alright so now we need to transfer the value from the worksheet to array so to do that let's see here worry a and B is equals to R such that that sells a and B alright so let's check this one first let me use a bit again now the started transferring the value let's check our RV if we get the first field so you can see that the first field is transferred over of a using a bit again yeah this is our header for Row 1 let me check now it's oh there's something wrong in here I should use eleven instead since we know how many columns we have so let me just put a break in here and then using a Penang and then run it then let's check our re first of all it's our header then we can see in the next row that this is our first record it's Rebecca did you yeah this is our first record in our raw data in let me check if we have until the last row populated the last row is Antonia let me check that yeah it's quick now we have the demo DITA transferred to our row away so the next step is to transfer this data to our search array so we'll just do the same for is equals to one two let's say to train a and Rose the town that and Excel app the crew next a and then for B equals 1 to 7 next B and search Ray C saturate a b c equals to ssh itself a and b in my face so let's run it again and check our search array the first column of course should be our header and the second row is for the first value and you can see that we only have first name and last name oh okay I think that we're good we can now start with our for loop matching this is a bit complicated might be for you right now but again this is this is very helpful when you will be dealing with a large amount of data that you need to much using a look so now we can perform our for loop matching so let's say the first loop should be one hour sir chorizo to do that for a sequel to 0-2 you bound oh no I'm sorry let's start with 1/2 you bound surgery next a and here we need to declare again the first name and last name variable string L nameless string so f name is equals to D surgery the row and then column number so I think this is one let me check and L name should be search array a and - let me check let me put a break in here oops run it and you can see that F name is first name okay we should start that row number two name is the Reta and okay it's quick now we need to loop on our go away so for B equals to two you bound Rory next B and then let's say a row away be in one is equals to F name and go away B colon 2 is equals to L name then make sure to add an exit point in here we need to capture the the other in from the the address it is state and from so that is three three four five six seven so it will be search ray a three sequels to Robbie B so the first one is addressee it's one two three four it's index 4 so let's copy there three four five six seven three four five six seven let's change this to four five six and seven right and this one - city and state cities six seven six seven the next one is pond one and even though one is two three four five six seven eight eight and the email is eleven or ten sorry alright now let's check this coded work we tried to run it and check our surgery if the previous blog column has been populated yes so we have now the other information as you can see you're not it's not showing a loading time when I run the program because that's how pass array is so now up since we have now the search populated the next step to do is to transfer back the search data of a value to our search data worksheet so to do that have another loop so transfer data so for let's loop on the Ave for a sequel to to to you bound surgery next a and then let's say for three I'm sorry for be equals to three to seven next be and then what's the name of power surgery SSH SSH that sells a be that value is equals to our search away a be alright and say debug the train process completed I want to check how long would it take so I will put here debug the train or not now say our main ten seconds in here and let's print another one once the process is completed so I think we can now try this so let's see completed sorry the fastest is now completed and you can see the it's almost one second to perform that matching or the for loop data matching so I hope you were able to grasp the idea of why to use array especially when dealing with large number of data so it's for you to decide based on the current scenario that you will encounter if you will be using the poor loop in the worksheet or it should be the for loop within an array so it depends on you and I hope that you learn something from this video if you have any question or anything related to B Bay coding if you want me to do some scenario for you just give me that scenario and I will be glad to make a video to resolve that or to give you some solution that I can do using VBA again thank you for watching and have a good day

Channel: Love Coding And Play

Views: 7,217

Rating: 4.967742 out of 5

Keywords: Love Coding, VBA, Excel VBA, For Loop in VBA, VBA Array

Id: hk6vXri91xE

Length: 17min 28sec (1048 seconds)

Published: Mon May 20 2019