Scenarios so we'll take a look at those with multiple practical examples and in the process you'll also learn how to write more advanced functions here we'll talk about the very important part of arguments different type of arguments and then i'll also introduce you to excel user form which is a great tool to design a user interface and also control user inputs variables data types loops um if-else statement.
These are very important components while writing any program be it in vba or being c-sharp or any program that you write it's it's the same concept so practice these three lessons multiple times and once you're comfortable with it you'll be able to solve again complex problems with with your program before we begin let's take a quick recap so far you've learned how to write subroutines and functions we've covered multiple function examples.
And subroutines subroutines where it executes series of codes and then a function that can also return a value after executing series of codes and then we have visited object hierarchy for excel where you've learned uh to use object dot and then you can you know like access the methods properties or events associated with it and then uh you've learned how to declare variables use variables understand different types of data data types and operators you've also learned how to use if else statement and also select a statement.
And uh do until let me first insert a new module i will call this so first let's start off with uh for each loop uh in for each loop you can loop through all the elements within a group for example you can look through all the cells within a range you can look through all the sheets within a workbook okay and then um so basically you're looking through items in a group okay so once i.
In selection so selection is this selection is arranged and then next excel yeah and if i print this out now in the immediate window i can do address maybe address first so if i press f8 so it says c2 c3 till 10 and then it exits so it it loops through each element each item.
Here in the selection for example in this example um so we can also similarly just return the value if i just rerun this f8 again so i start looping now it is this value so every code that you put within this for and next is going to be executed okay okay so that's basically how you'll use for each loop now here we are using a range example a.
Cell example but it could be anything so i'll demonstrate more examples now let's say that in the process of looping uh you want to exit once the condition is met you don't have to look through all the elements then you can use exit for okay so for example let's say um in this practical example let's say that oh you want to find a value which is this once we find this value we want to exit okay if excel dot value is equal to this okay this is number comparison so.
I'll not put double quotes that will become string if that is true then we'll just say so dot interior dot color is equal to maybe maybe green maybe we'll just put a color and then exit okay so it's gonna go through oh sorry um i didn't select all the cells so instead of selection we can also write range and then within the range we can.
Specify c1 c2 till c10 yeah so c to until c10 now we want to look through all the cells in this range yeah if you want to even be more specific sample sheet you can specify in this particular sheet like this it's always a good practice to be always specific because um you know if you not specify the name of the sheet is it could run in any of the sheet based on how you're executing the code so i'm gonna go through now so first.
- How To Design An Onboarding Screen Using HTML, CSS & JavaScript - Part 2 BlogSpot
- 6 Steps Process Powerpoint ***FREE DOWNLOAD*** Infographics Presentation
- Cara Mudah dan Cepat Bikin Pas Foto Tanpa Install Aplikasi
It is excuse me the uh c2 is not this value so it's gonna keep - ID Card Make
Going till the condition is met so i'm pressing f8 uh okay hold on hold on for example in sorry it was my bad it's to b6 okay and this one i was thinking why you didn't run okay let me run again it's just gonna go through.Now this is condition is true so it highlighted that value and now it it just kicks this off right as soon as the condition is true and then it it stops looping it did not go for this particular value again so if you want to exit you know like um in the process of loop if you want to exit away from the loop then you can use this particular syntax now let me show you more examples let's say that we.
Want to convert all these values to text then we'll just say convert to text or string it's the same thing and then here we can say that this value is equal to excel within double quotes you can put a single code so anytime you want to make the number to string you just need.
To put a single code in front of it okay that will convert the number to text okay so here i'll just put a single code and concatenate it with the existing value okay double uh first i'm putting a single code and then whatever value at well it was looping through now let's run it again i'm gonna press f8 so you can see this is already converted okay so that's how you can convert it to uh by looping through all the.
Cells uh let's try a couple of more examples these are small examples but it will give you a good example of how you can use this for each loop let's say that you want to convert it back very quickly to number then i'll just multiply this number by one so for example if you do this manually into one okay sorry to number okay i hope you saw that error this was ambiguous name detected that.
Means there is two uh procedures subroutine with same name so you'll have to change that i'll just do it okay and now if i run this code it just change back all this to a number um let's say that let's say you want to consolidate um the data from multiple sheets by looping through all the sheets i'll just demonstrate that let me open this dummy file.
So here in the pivot table let me select one life and then i'm gonna create multiple tabs just for this purpose um okay and then i'm gonna prefix it with maybe data something like this and the code will loop through all these uh sheets and then if it finds any uh the sheet name prefix with data then we.
Will consolidate that to a master data master sheet like this so let me just make a quick placeholder it's going to be clients and then multiple quarters so let's say that you want to look through all the sheets and then when it finds any sheet starting with data dash then we want to copy the data from there and consolidate it here in the master tab so you can utilize for this kind of.
Scenario you can utilize this i'm just gonna sub uh console consolidate data and then in this scenario i'll declare sh as worksheet and then in the same fashion we're just gonna look through okay but then instead of for each cell it will be for its sh which is a worksheet which is a sheet uh in active workbook.
Active workbook because we are writing a code in our this project file
But the data is in transaction so for now i'm just gonna make just run this code in whatever workbook it is active and then i'll first maybe print out the name oh sorry this would be worksheets and then sh so if i try to print this out now.So it's printing the name of the sheet transaction ac and tql and so on okay all right so now let's see that um if the sheet name is equal to data then we want to do certain thing which is to copy the data right um now we want to just compare the five strings on the left side right so.
I'm just gonna do it to left and then this five now if i just test out this in immediate window here i'm just gonna copy this so i'm saying left of uh this particular string return five characters right so one two three four and five so it returned me that now i'm saying if left of this is equal to this then we want to do a certain thing yeah so i'm going to cut that out and then.
Let me just print it out again just to check if this works i'm going to just run this so it returned only the sheet name that has data in it now anytime you can easily generate a code using a recorder you do not need to type those things okay so i'm going to record a micro i'm going to go here i'm just going to activate this particular sheet copy this data as a sample copied.
I'm gonna come to the master tab i'm gonna paste it here as values like this and then i'm gonna come here you can see paste is still enabled once you press escape this should be grayed out that means the clipboard has been cleared okay now always do that practice because if the clipboard is not cleared and you try to copy something else and it fails it's gonna replace the same one that is in the clipboard so watch out for that.
Um where is our code so i'm gonna copy this code which was recorded right now and i'm gonna bring it to loops demo and then here i'm gonna paste the code right there so in order to um and then you just need to press tab tab from your keyboard and then if you want to um and then towards the left you can press shift and tap from the keyboard okay now uh want to activate this we don't.
Want to activate this particular tab all the time we want it to be dynamically uh as as it loops so we want to select this or you can activate it's the same thing now for the range let me just leave it like that and then we want to paste special values in e2 and then this part is irrelevant so i'm just gonna get rid of it delete and this part clears the clipboard okay now we already know how to make the rings dynamic so i'm gonna go and copy that.
Uh where is it this part so dim let's say last row as long so we're saying last row is our variable name and it's gonna consist of a number data type long date. and how much of numbers that it can contain you already have that summary table so you refer to that now from here we want to check if.
Uh what is the last row from this particular tab we're saying last row is equal to um dot rings e i'm gonna get rid of that now we do not want to copy the grand total so i'm gonna do minus one for example if i execute this right now for this step let me just bring this side so we are able to see the data so every time you put a question mark and execute a line of you know like code or expression it's going to return you a.
Value so it returns 12 right now um if i run this code now it still copies a specific a7 to you know e12 so instead of e12 i'll remove 12 and then i'm gonna concatenate and reconstruct that particular range address yeah so this is our new address now this part is always going to be dynamic the last row based on how much data it has in that particular sheet now if i run this code it will still.
Paste in a2 so we need another um variable which will uh always paste it to a new row so i'm going to construct a new variable called new row and then this new row will be based on the master sheet this equal to this and then here i'm gonna maybe it's already selected so it doesn't matter but i'm just gonna put master sheet there and this is gonna be plus one so whatever is the last row that it finds i'm gonna write it next so.
This particular line i want it to return plus one for that purpose now instead of a2 we want to write it to instead of row number two i'm gonna do e and then whatever is the new row it identifies okay so let's consolidate the data by running this code uh let's see if this works yeah so it it'll select the sheet it's gonna copy the data now come to master tab now you can see.
New row is two so it's gonna paste in a2 it's gonna clear the clipboard if you observe here and execute this line it clears the clipboard and then goes on to the next sheet which is data now if i scroll this down you'll be able to see it copies dynamically to the last second row yeah and then i'm gonna come here it pasted there similarly i'm gonna run f5 run.
Everything so it consolidated all the data from all these types okay so that's how uh you can leverage um looping true objects for example you can look through the series of chart uh you know items or pivot field items you can look through you can make certain things visible based on your criteria there are so many things you can do as we go ahead we are going to explore more examples because loops will be used in almost all the programs so we'll definitely come across this.
Example again again if you want to look through instead of sheets this can be a workbook again this could be pivot items it could be so many things because all these excel objects that you see here is a collection of items right collection of elements in a group all the workbook has different sheets charts pivot tables uh range um shapes it could be so so many things okay so that is all for each loop here in for each loop.
We are looping through each element in in in a group right now for next loop in for next the syntax is four and then you can specify number of times you want it to look okay you can specify a number or a counter for example let's say for i is equal to 1 to 10 now and then you can put next i and then we can say dim is long in this example this is this code is going to run from 1 till 10 meaning this is just the.
Beginning counter and the encoder okay it's going to run from 1 till 10. if you specify here 100 is going to run 100 times you can also do from five to ten for example it will run five times and so on so let's try uh one example i'm gonna use the same table again let's say that we want to loop through uh from two to ten you can specify in this fashion now if i do debug dot print cells now in cells you can take.
Row number and column number instead of specifying the hard coded number i'm gonna press i i will change as it loops this number i is gonna change from two to ten and then column let's say it's one dot value now if i run this line by line so this it return this right like this okay now similarly like the first code.
You can also use here exit for again if you want to exit away from the loop exit the loop uh as it goes on dude you know like based on your criteria you can use this exit for that example and um so let's try more examples just to get uh more clarity on how to use this let's say that we want to look through and multiply each of this with maybe let's say 10 will increment with 10 uh what we can do is i'll say this.
I'm just going to comment this out like this is equal to this value into 10 yeah so we're saying whatever value it was at that time this is not going to be one this is going to be c which is third column one two three yeah so it's saying that this value will be multiplied into 10. this cell value is equal to this cell value into 10 yeah so let's try that again these are operators.
So you see the number of change yeah so it's looping through it uh you know like for this counter and then it's going all the way down to the bottom and then once all uh it reaches 10 it exits okay so here in this in this loop this is one of the loop that we use most often and it's very is the most easy one as well to use and it has more features than this now let's say that you want to look from.
From here to here and then um you can spec you can do in that fashion as well for example from 10 to 2 as well you can do that as well so for example now let's say that uh we want to multiply by -10 now if i run this you observe it from down here okay it's going to look from here upwards we can also just color it as we go on we'll just use this.
So we can see okay uh okay sorry so while we're looping backwards uh i forgot to mention this you can use step to increment or d like to increment or you decrement it so for example in this one you can use step minus one okay so in this example it's saying that we're going to loop from 10 to two and then we're going to go backwards from here to up like this now if i execute this code.
It print amanda which is at the bottom at the moment and then um if i run this code again so it colored this right so you multiply by minus 10. uh going back so it multiplied uh into minus 10 and then it converts it back uh to green that cell that way you we can visualize where it's running okay so that's how you can loop downwards and upwards so let's say now that.
You want to um color alternate rows like this for example let's say we want to put uh this color the easiest way to identify the rgb for this is rgb red green and blue is just to go to more colors and see the from the custom tab here so 242 i'm gonna come back here this is the third example let's say we want to look from two to ten and then we have already done step minus.
One this could be step one step three increment it however you want it so let's say we want to go from two to ten but then we want to increment it two times um in this example i'm gonna say cells dot resize we'll just resize the address one row and then one two three four columns the interior dot color is rgb the the color that we just saw 242 is it so 42 to 42 to 42.
Like this now if i run this i'm expecting that it will color alternate rows in this table i'm gonna run this okay okay um this should be one sorry about that resize from the column one yeah now if i just remove all the colors and re-run again okay so it all in alternate rows are colored now you can also um let's say you want to insert blank rows for in each of this table.
For each row in this table we can also try that by using the same example here it's going to be for example for each i mean for for next loop i keep for it keep saying for each now let's say that um on the two and then here we can just get rid of this now we want to insert right so once this rows and then we'll use i for that row and then we can use insert now if you're for excel there are.
So many ways you can generate the syntax so if you're not familiar with anything it's just best not waste your time and record it for example let's say i want to insert a row insert it stop the micro just go and copy the syntax so it's insert shift down yeah that's the code now if i go back to loop i can just put it here but instead of hard coding this particular row this will become i this will be whatever is the counter at that point of time.
