alright so in this video we're gonna learn about navigating through our workbook using VBA code and
basically just some basic debugging practices that we need to know so what I'm gonna do here my code
that I wrote here so far is in book 2 I also have booked reopen but I'm not gonna need that so I'm
gonna close that one so I'm gonna just move this thing over here and go back and open my code I'm
gonna leave all of this as ease I'm gonna create another subroutine here we'll call it sub navigate
yes and that will be the name of our subroutine so now let's try to move around our worksheet
using our VBA code so let's say I have my cursor right now in a1 C cell that's my active cell I
want to make my d7 cell my active cell so what I do I point to that range or cell using range
or cells object so I'm gonna start with range object and do I'm not sure what I said I think
I said d7 it doesn't really matter so that's the cell now I want to navigate to that cell so what
I'm gonna do I'm gonna do dot select so if I run this code we should see C it goes to that d7 cell
now you might want to move from the cell that you're currently in up or down or left or right
so the way you refer to the current cell you're in that's gonna be your active cell from that
active cell we might want to go to down so the way you can do that is by using offset so use offset
and you say how many rows you want to move down or up and how many columns you want to move right or
left so if I want to move let's say 2 rows down that would be 2 comma and if I want to go one
column to the right that would be 1 and if I want to go to that cell I will do dot select again so
right now it's gonna go to d7 and that will be our active cell and from that active cell we're gonna
go to down want bright that would end up being e9 I believe so let's run that see there we are
een ight now this offset let's copy and paste this now we're gonna be at this point at een ein offset
can also be negative so if I do negative 4 that means we want to go four rows up so that would
take us to the 6th wrote that point and if I want to stay in the same column I would do zero offset
if I wanted to go left I would go negative column offset again I'm gonna run this see that brings us
here now very often when you run through code you might want to see what's happening step by step
right now when I run this code I can barely see what happens I can only see the end result when
it gets to this last cell if you want to see the steps one by one you want to use the f8 key on
your keyboard just press on f8 key some times to get to that f8 key you will have to find the FN
key on your keyboard and do FN f8 to get to that but basically that's the f8 key so what's gonna
happen when I press my f8 key right now you'll see it's gonna highlight this subroutine that
I'm currently in in yellow it's basically telling me I'm about to run that line so so far it didn't
run the line that is currently on so if I do f8 see it moved on it started the subroutine now it
went to this range d7 select but we still haven't done this line so when I run this f8 again it will
run this line so f8 see we went to d7 so now the next step is to go to rows down one column right
so if I do f8 again we can see that happen in the background and finally the last step is to go four
rows up from here so if I run that again we should see that happen now one thing to remember when you
run this is that if you still see the yellow that you're still in this debugging mode when
you run the code line by line by line you want to make sure you get out of this debugging mode so
you either have to step through enough with f8 to get out of it or you just go here and reset it by
clicking on this reset button so we click on that we get out of it now we're back to our normal mode
basically so yeah if you wanted to run it step by step you do f8 to debug and that basically steps
through the code step by step alright so far so good so we've learned how to navigate to different
cells we can go to cell now let's select a whole range so what we can do we can do range we can say
from c2 through d6 dot select so now if I run this code see we'll see the last line it selects from
c2 through d6 so with ranges and cells there is also this method called activate and sometimes it
may feel like it's doing the same thing as select so let me show you an example of that so if I do
something like range III dots lacked let's also do something very similar to that you like efore
only instead of select will do dot activate so let's see what happens so I'm gonna step through
this code with f8 see we get to this range we run that it selects the whole range I've run
it again we select III cell in a background and then I run it again and that selects the e4 cell
in a background so it seems like we did pretty much the same thing with select and activate now
to see the difference between the two we have to kind of deal with ranges so right now if I
remove those lines and I run this see we have this range selected from c2 through these 6 but see
there is one cell that's kind of like highlighted that's the c2 that's currently the active cell in
this range now let's say I want this d6 cell to become the active cell while this whole thing is
selected so what I can do now I can do range d6 dot activate and let's run this and you'll see
it's still selecting this range but now the active cell is these six instead of c2 so that's what
activate does it makes the active cell basically go wherever it needs to go so when you're doing
with a single cell it works pretty much the same way as selecting but otherwise you know there is
difference when you deal with a range so we should be able to also activate some cell in the middle
so I could just do c4 and run this and see that c4 now active and the range is still selected
alright so now we can select ranges we can select cells we can activate cells let's try to move to
a different worksheet or tab so if I wanted to select this sheet one I would need to move to
that worksheet first so I can do that by doing worksheets and then we either use the index or
the name of that particular worksheet so I'm gonna start with the name so I'm gonna do sheet by the
way this is not case-sensitive so I'll do sheet one and then we do that and then we'll do select
to select that worksheet and this should also by the way work if we do sheets so if I run this see
the last step went to sheet 1 now we could move to the second worksheet by either referring to the
name of the worksheet or if you remember the first video we can also refer to our sheets by the index
so the second worksheet or the third worksheet something like this so if I run this see we went
to the third worksheet so we can now move through different worksheets as well so that select will
basically just select it so think about select sort of like clicking with your mouse action in a
way it's not exactly that but it kind of works the same way so we replicate our user going here and
selecting a range or selecting a cell or clicking on a worksheet to go to that particular worksheet
we can also move through different files so let's actually again create another file I probably
shouldn't have closed that one but it doesn't matter so now I have another C workbook this is
book 3 actually it's book 4 so that's the name of the workbook I'm gonna go to book 2 so if I wanted
to go to that workbook I would do work books and the name of the workbook is book three now with
workbooks we don't select them we activate them so if I run this it's gonna do all of these steps
so it's gonna go to sheet 3 on this worksheet as a last step and then which is gonna be not sheet
3 as a name but the third one which is this crazy name I typed here and then after that it's gonna
go to workbook 3 so let's run this oops so that didn't run because that was not workbook 3 it was
called book 4 there it is let's run it one more time and we have our book for activated so we can
also go through different workbooks we can also go back to our first workbook by going to book
2 again so let's in the middle maybe we'll make a different cell in our workbook for active and
you can do it with range or cells the same way so I can do 3 2 dot select to select that cell
the third row second column so I'm gonna just go to book 2 and open this and step through this code
step by step so we can see what's gonna happen so f8 and keep pressing f8 so that went to d7
we went a couple of down one right we're off set it then we select the range then we activate the
cell then we go to our sheet one then we go to our third worksheet and then this should go to
our book for there's our book for activated now then I'm gonna select the cell 3 2 we're gonna
go back to book 2 looks good and then that's the end so that should help us navigate through our
workbook and last thing I'm going to mention here before we finish this video is that we can also
refer to our workbooks using their index as well by saying the first one the second one the third
one so I could then say the second one and then the first one I should have two workbooks in here
so right now I'm on book two let me run this so we can see what happens so I could do f8f8f8 to
go through this whole code step by step but when you have a lot of steps you may want to just run
a lot of this code in the beginning and then step through step by step at a certain point so I just
want to get to like this part and execute all of this so I just go here and create a breakpoint
break point is you go here on the left side in this gray panel you click on the line where you
want to stop so it's gonna run all of this line on top and it's gonna stop on this line when it
needs to activate this book for so I'm gonna go ahead and just click run on top see it ran all of
that it stopped at this so from now on I can do f8 key that goes to my book 4 then I do f8 key again
that selects the cell which was already selected then it's gonna go to book 2 then we're gonna do
workbooks the second one we're gonna activate and the second one is gonna be book for in this
case and then we're gonna go back to the first one which is right there and then I'm gonna stop
one thing to remember is that when you create a break point don't forget to remove it so you just
remove it by clicking on that red thing on the left and it's gone so I'm gonna go back to excel
here we can close this window by clicking on this little Excel thing so all of these macros that we
created are also going to be accessible here under macros if I open this everything we created it's
gonna be here and this is the one we were just working on this navigate and if I run it should
basically just do all of the steps similar to clicking run from that other side finally if you
want to save your code you go under file and save and you want to make sure that you save this as a
macro enabled workbook so we give it some name and then here instead of regular Excel workbook we're
gonna switch it to excel macro enabled workbook otherwise you're gonna lose all the code you wrote
this is your filename called whatever you want I'm gonna hit save and that should just save this and
that's it for this video thanks for watching please subscribe and I'll see in the next one