Excel VBA Basics #3 - Using For and Next with variable, using loops for custom reporting

hey there youtubers thank you for tuning in this is Daniel strong with excel vba is fun we're back at it again we have an empty workbook here I'm still working on the test one procedure here I've erased the cells and range objects that we have previously been working on today we're going to talk about a simple loop we're going to use the for next procedure and I really don't have a better term for that we're going to talk about variables very seriously we're going to use X again I use X all the time it's simple and I remember it all the time we're going to use X as our first variable and just keep in mind this can be any variable you'd like you can have blah to be equal to 1 or you blah to be equal to the word blah or anything let's go ahead and run this oh I did I did say I wanted to talk to you about the stop using this stop so I know what we're going to do right now let's go ahead and run this and you'll notice that it's going to stop at your stop marker instead of proceeding to the end and then further more you know stopping everything so if I hit play you notice it's already run everything to this point so blah will actually equal 1 blah is my variable Blanc is equal to 1 and we've stopped instead of continued and finished off our procedure we stopped right here and sometimes you might put little stoppers in there be sure to take them out later you'll be sorry you put them in there to stop it if you wanted to see what's going on why is it not working at this point right here what is all the what are all the variables equal at this point so that's how use the stop so I'm going to take that out I'm going to change that to X again so we can work with X and do a simple loop right now x equals 1 and we're going to use that in just a moment I'm going to tell you a little bit about the 4 and neck feature that I have learned so you could say for X notice that's our variable for X equals 1 all the way to let's say 10 for X equals 1 to 10 when I hit enter it's going to clear that up you'll notice these are words are in blue that means that they have some power to them and I'm going to hit enter a few times and then I'm going to say next X next variable name we could put blah here and blah here if we had already planned on blah being equal to 1 and furthermore 2 & 3 & 4 all the way up to 10 that's what we're going to do when we run this so we talked about the cells object we're going to go ahead and use cells and when I open the parentheses I'm going to put X in there that's because X is going to change every time we go through this it's going to say use the next X that means increment it by 1 so it'll be 2 next time we go around and then next time next X is going to be 3 and all the way up until 10 this is going to be very exciting I promise you so we're telling it cells X comma column let's say column 1 okay we'll have that equal to how about X whoo now it's getting interesting what I'm going to do right now is every time I have a for next statement I like to I'd like to tab this out so it makes sense what's going on everything inside this little box here from here to here has been tabbed so that people know this is what this is with contained within these boundaries here and here and you'll learn you'll really learn when you start playing around this that using tabs correctly will help you be able to read what's going on instead of just a big blur of everything lined up so let's do that now I'm going to go ahead and start debugging you probably have no idea what's going to go on but what we're going to do is we're going to put we'll start with cell 1 comma 1 that's a 1 we'll be equal to one and then next time around well let's just do it I'm going to hit f8 so right here we've just turned X equal to one now x equals one to ten all right guess I didn't even have to to do that part there because this is defining the variable right here x equals one to ten so first of all sales and when I have over X no matter where it's one so let's do that sells 1 comma 1 equals the value of 1 let's hit f8 and sure enough one one row 1 column 1 is a 1 now when I go to next X you look at X and now it's become - because we're counting from 110 so now let's let's see oh this is going to be equal to 2 and sure enough it was row 2 column 1 hope excuse me let me dismiss my email notifier okay moving on we're going to we're going to see here that each time it increments X it's going down one row and turning and turning it into that value so I'm going to go through here in fact I'll hit f5 to just complete the macro and we see that it sure enough it did that I'm going to erase that now what if we what if we wanted to make it a little more interesting okay what if we wanted to say that it is equal to X plus 2 each time so let's follow that really quick when I hit when I go into this it gives it the value of 1 so this is going to equal 1 plus 2 that's going to be the value of 3 right there we move to the next one and we're going to we're going to this cell 3 comma 1 that's going to be right here however we're telling it we want it to be 3 plus 2 X plus 2 so each time we have it to be that we can use multiplication we can use anything that we want in here so let's do that let's do times three okay each time we loop it it's going to go and say x times so one times three is one the next one is going to be two times three is six then nine then 12 15 18 and I'll hit f5 to complete them here just quickly it blasted through so multiplication it can blast through extremely complicated math that's the nature of Excel so that's all that's a real quick loop of course you can have text if you'd like and that will come in handy sometimes I'll hit f5 and show you that it immediately I went it went one at a time but it goes so fast you can't see it I'll do it one at a time just to show you f8 cells right now it's one now it's two three and so on so until the end so let's settle interact with numbers and math and all kinds of awesome things then if you're running reports which eventually we're going to get to that point you're going to have multiple things going on whatever we made a certain condition so let's let's do something interesting here we will do something in column two right here so cells x comma 2 that means column 2 so we're going to be using column B as well is going to be equal to well let's do that let's do x times 15 okay so in the first column it's always going to say blah or we could always have it have today's date or something let's put date now if I type date in here you notice it capitalized itself that tells you something it tells us that Excel has of on a predefined variable called date you can't mess with that one this always equals today's date in it so let's do that really quick we're going to go through I'm going to go slow 1 1 that's a 1 is equal to today's date and you notice when I hover over that it automatically knows what I wanted to ooh make these a little big enough so and then when I go to this one we're going to cells 1 comma 2 that means Row 1 and second column that's going to be B 2 B 1 is going to be equal to 1 times 15 because X is 1 right now so you see that they're 15 now when I go to next X I hit f8 now it's of course - so we've got this is going to be the date each time sometimes you might want to be this today's date every time you run a certain macro or whatever equals x times 15 well X is 2 currently now it's going to be and so I'll go ahead and run that to the end but you put today's date and column 1 and it put whatever we wanted to increment x 15 each time so that's something fun you could do and of course you know I think you know that you can manipulate dates using plus/minus if we wanted to use tomorrow's date of course that'd be date plus 1 and that would be tomorrow's date so let's run that if we wanted to always be tomorrow's date you would run that it and then it says - 18 instead of - 17 if we wanted to use next week date plus 6 I believe that would take us to a week from today so that should take us to the 24th of February so let's try that let's see here I'm going to hit f5 and just run it no date plus 7 let's run it again f5 okay so the 24th that's that's next Sunday so if you wanted it to be a week from today in column 1 and if you wanted it to be whatever you want there and column 2 that's how you run one of those you could furthermore if you have a lot of columns if you're running a custom report you might want that to equal the word pizza you know there's a lot of things you might want it to equal and I use a lot of if-then statements you're going to see those later I'm going to go ahead and run this you see pizzas now and column 3 because we put current ro+ always third column you can put variables in there we're going to talk about loops within loops and all kinds of fun stuff thank you for tuning in again god bless you and thank you for watching excel vba is fun video

Channel: ExcelVbaIsFun

Views: 325,493

Rating: 4.8399262 out of 5

Keywords: for next, vba basics, loops, loop, Microsoft Excel (Software), macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba tutorial, visual basic tutorial, range, excel, excel tutorial, मैक्रो, Microsoft Excel (सॉफ़्टवेयर), مايكروسوفت اكسل (البرمجيات), دقيق, Makro

Id: E6wtxAsSQRY

Length: 11min 12sec (672 seconds)

Published: Thu Feb 21 2013