Excel VBA Basics #4 - IF THEN statements within the FOR NEXT loop

hey there youtubers is Daniel strong with excel vba is fun thank you for tuning in again this is our and this in this segment we're going to continue our loops study using the for and next and using a variable of currently X what we're going to do immediately right off the bat I want to give this some titles for our custom report here so I don't want to actually have this to be writing in column one excuse me row one right off the bat I want to take it from 2 to 10 so I'm going to tell it 4x equals to Rho 2 to 10 and as you'll see if that way it will not overwrite our it won't or overwrite our column headers so let's put date here let's put remember what we had it here I know we had pizza in here why not we'll call this the food and we had the we had a random number well call it number let's go back to our Visual Basic and coincidentally if I exit it out of there oh no what do we do you know how to get there we're going to either click on here I'll and it shows alt F 11 we're going to hit that you're going to get real good at hitting all f11 to toggle here and the visual basic editor okay alt F 11 will get you there immediately without clicking anything so we're back here we're going to go from 2 to 10 without hesitation we're going to go ahead and run this we're saying date plus 7 that means a week from today is always going to go in column 1 and in column 2 each each time we loop through 2 3 4 5 6 all the way to 10 each time column 2 is going to be whatever the current row number is times 15 and in column 3 where we have food we're going right now it's just going to be pizza so we're going to have some fun with this with some if-then statements in just a moment right now I'm going to run it and show you what happens when you just run it as it is I'm going to hit f5 which you can see by hovering over the play button I'm going to hit f5 with while selected within my procedure okay here's what happened as we see just like before only it did not overwrite column 1 because we took it off what would happen if we put 1 to 10 well you know what's going to happen it's going to put the date here it's going to put 1 times 15 here and pizza there I know one thing I want to note here if you are running macros you if you're doing something that you're not certain what's going to happen and this is going to happen in the beginning you want to you're going to want to save your workbook before you run the macro because there is no undo button you can't click on do and undo a macro it doesn't save as it's progress and once it's it's done it's done so you need to save right before you run it that being said let's play around a little bit with this food object here we're going to use what's called an if-then statement right here so I'm going to take this and hit tab now we're going to hit backspace there we're going to say if let's do something with with cells X comma 2 let's do that if we're going to have it analyzed the number here let's say if it's an even number we're going to put a different word than pizza and if it's you know if it's regular than who cares it will leave it alone so we're going to say if whatever is in cells x comma 2 which we just wrote on there before if that is well no let's just say if it's greater than a hundred because we have a few that are going to meet that criteria if this is greater than 100 then and you actually have to type the word if and hit a space and when you type your criteria you type a space and put thin and in formulas you just put commas but this were actually writing an actual innocent statement if this is greater than the value of 100 then we'll go ahead and let the next one be equal to pizza and then I'm going to hit enter and I'm going to shift tab that'll take you back and forth shift tab shift and shift tab I'm going to say end yes this is very important to put an end if and there if you don't it's going to give you an error I'll show you that right now they're going to say you never closed out your if statement we don't know when to stop I'm going to start this so I hit f5 to run it and sure enough it gave me one of these errors let's put in if so we've ended that statement you notice I tab this one out here so we can see what's exactly contained within our if statement it's easier on the eyes it's not required to run so let's do that I'm going to erase all this stuff here let's see what actually happens when we step by step this so I'm going to hit f8 so now we're on X has the value of excuse me I don't know I guess I need to change that back excuse me let me stop that and start it over we want to go from two to ten if we want to 1 to 10 100 write our headers from X for x equals 2 to 10 so now x equals 2 next time around it will be 3 and so on so Row 2 column 1 equals next week 8 plus 8 plus 7 is next week we've done that now this one is pretty standard 2 comma 2 so that's going to be B 2 equals 2 times 15 so it's 30 now here's our if statement if whatever is contained if that number is greater than 100 then let's see what happens when I hit f8 oh they're saying that 30 is not going to not in their correct let's go to the next one three okay so column RCB Row three if is that greater than hundred skipping it they didn't write anything in there and let's go on sixty seventy five no ninety nope now let's watch this and I hit f8 right here they're saying if this is greater than 100 it is so now when I hit f8 instead of skipping over right here it actually is going to take in consideration all the different orders that we have we only have really have one currently but if we had a big line of procedures it could do that oh I threw it off I direct you can drag your where you want to be also by moving this yellow error we were here so we're back here again this equals pizza now that the value is greater than 100 and true enough I put pizza in there and we'll hit f5 and well run through it a couple more times this 120 is greater than 100 yeah so pizza and the next one 135 is greater than 100 so pizza so if I hit f5 to complete it all the ones that were greater than 100 right here and this excuse me this column I put the value of pizza now how about we put an else that means otherwise if I hit here I'm going to shift tab I'm going to say else or otherwise is what that means hit enter hit tab so the spacing is correct we're going to say otherwise the cell of is the same cell is going to be equal to not pizza and so let's try that now so we're saying essentially if this is greater than 100 then put the word pizza otherwise instead of leaving it hanging and leaving it blank otherwise say not pizza let's run our macro again and first let's debug it because I want you to see what happens I'm hitting f8 several times to get to here we already are running so I said if this is greater than 100 30 is not greater than 100 so it immediately skips over this and goes to the else and everything that's in the else column so otherwise this is going to be not pizza so let's expand that a little bit so we can see everything going on let's see is 4,500 no not pizza 60 nope not pizza 75 nope not pizza 90 not pizza and 105 is going to be pizza so it's a little quirky way of learning that but that's a good enough example I'm gonna hit f5 and sure enough at random macro analyzing each number and when it got over our threshold of 100 it's a pizza instead of not pizza and of course you could change that to be a number zero and I'll hit a five run that zeros okay or you could have it be the value of false which is an actual value notice it's blue and it capitalized itself that's a value true or false or values so we'll say it's going to equal false it's not going to be valid or something and we could have this be equal to true we wanted it to be true if our if it was over a hundred and it doesn't have to be food we could say you know bonus if our bonus you know if we made over a hundred sales or something I'll call this sales just to make the numbers more real if they got more than a hundred sales than true otherwise we'll say false okay let's run that now and sure enough it has done this values accurately again we can run this macro by hitting all f8 or clicking the macros button there let's hit alt f8 okay and we can run test one this way also good by double clicking on it or hitting run alt f8 again from scratch let's hit run and there is our macro lightning speed thank you again for watching this we'll be doing more on loops and all kinds of things with variables running custom reports from special menus and all kinds of things like that filling list boxes combo boxes working with again user forms and we can use buttons on a page and all kinds of things like this to where when you click on the button it'll run the Mac in fact I just ran through that you'll probably want to see that in slo-mo but I just assigned our macro to this button and we'll be doing all kinds of fun things like that so thanks for watching and God bless bye

Channel: ExcelVbaIsFun

Views: 276,682

Rating: 4.921865 out of 5

Keywords: excel vba if, excel vba if then, excel macro if then, मैक्रो, FOR NEXT, LOOP, vba if, vba if then, if then else vba, IF THEN, sub, vba basics, vba tutorial, macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, thisworkbook, object, visual basic tutorial, range, excel, excel tutorial, excel basics, LOOPS, vba sub, procedure, تتفوق vba إذا, تتفوق vba إذا ثم, excel vba kung, एक्सेल vba अगर

Id: hS6yCdvyeA0

Length: 11min 35sec (695 seconds)

Published: Thu Feb 21 2013