Sign North Dakota Permission Slip Later

Document type sign permission slip north dakota later

hello and welcome back to tips and time-savers I'm Danny rocks and in today's lesson I'm going to demonstrate how you can write and then use a macro when you need to quickly fill in missing cell labels blank cell labels so over here a colleague has sent me a report but notice all of these blank cells over here so this report may have been created by a pivot table or could have been credit using a relational database program such as access so it's a nice-looking report and I have a summary of the region and then within the region each of the individual states in that region and their sales however what I want to be able to do I want to be able to create custom filters and possibly create my own pivot table before I can do that I must fill in these blanks cells the missing labels so I've listed over here my options I could make a copy select make a copy with ctrl Z and then highlight the range and ctrl V to paste but you know that's rather tedious isn't it so I'll use ctrl Z to undo that if I only have a handful of missing labels I could use a very fast keyboard shortcut ctrl apostrophe control apostrophe will copy the contents of the cell directly above so if we have just a handful that's terrific my third option is to use the autofill handle so over here if I want to copy this label Midwest down into this blank area what I want to do is select it and then move my mouse to the lower right corner and then drag to autofill a copy of that label now that is error prone because I guarantee that sooner or later when you do this you'll get distracted by a phone and you're going to over copy one or more of the labels so that's a no-no what I'm going to demonstrate is the basis for a macro I'm going to show you how we can first make a selection within this data range of only the blank cells and then write a formula that will copy the label that is in the cell directly above and then copy and paste the formulas into values that will become the basis for our macro now I've already written them background I want to show you what the macro will look like in terms of code so on the Developer tab of the ribbon come over here and I want to show the code the visual basic code over here so this is the code that I have they're really just reliance of code and it will perform this function very quickly as you will see I'm going to run the macro right from here so watch what will happen when I click over here to run and run the macro bingo you see how now all of my blank settles are filled in all those blank labels are filled in now we'll come back and examine how this works in just a minute what I want to do is I'm going to work on a copy over here so let's think this room what I want to do is of course number one make a selection of any cell inside the dataset next I need to find a way to quickly select all of the blank cells fortunately that's easy to do I'm using Excel 2010 if you're using 2007 it works the same way Home tab of the ribbon and come over here into the editing group notice that there's a drop-down next to find and select and we have go to go to special and within the go to special dialog box these are the most popular special selections to make so let's open up the go to special dialog box now if you are using Excel 2003 let me show you another way to open up the go to special dialog box use the keyboard shortcut ctrl G G stands for go-to and then you have a special button down here so here's our go to special dialog box we want to select make a selection of only the blank cells so when I click OK notice that now all of the blank cells are selected inside the data so now we can write our formula so I'll use the equal sign to begin the formula and this is a very simple formula I use the up directional arrow in other words I point to the cell directly above of the blank and what I want to do is I want to copy the value of the cell directly above now remember I have made a multiple selection by using the go to special blank so instead of hitting enter which would enter the formula into one cell only I want to enter it into all the cells in my selection so I use ctrl enter so now I have filled in the blanks but remember that these are still formulas so the next part of this will be to make a copy and then paste the formulas especially as value so I made a selection of the entire column a I'm going to copy and then come back here and I want to paste special the values only and I'll use escape to remove that selection from the clipboard all right so that works really really well now let's come over here and let's take a look at how the macro works so if you're using Excel 2007 excel 2010 you first must make sure that you have the Developer tab showing all the ribbon now I'm in Excel 2010 here's how we make sure that the Developer tab is showing go to the file button which opens up the backstage view we want to come down here into options and then we want to say customize the ribbon and over here notice that these are all the tabs by default the developer is not showing so we want to make sure that we select that if you're using Excel 2007 the way that you add the Developer tab to the ribbon is the office button and down here in Excel options on the popular tab you want to make sure that you check show the Developer tab in the ribbon so you don't do worry about the developer in Excel 2003 you don't have the ribbon so I have the Developer tab which means that I can write and then store macros so I want to open up that macro click over here this is the macro that I want to examine so I'm going to say I want to edit it this opens up the visual basic the VBA dialog box so I first off have to create a name for the macro and all macros begin with sub sub stands for it's a shorthand for sub procedure so sub and then give your macro a name all macro names must begin with a letter and they cannot contain spaces notice that we also have a left and a right parenthesis so it resembles a formula now over here in the green font and with the apostrophe that's a comment so I want to remind myself a month from now what this macro is supposed to do so I wrote the the comments in by putting an apostrophe first and the apostrophe turns whatever I write into a green font by default so this macro will first select all of the blank cells remember how I use the go to special dialog box to select the blank cells and then it will use a formula to copy the label in the road directly above and then paste that formula as a value so three lines of code this is an optional line to put it there in other words if I'm going to come across an error I don't want this macro to come to a screeching halt so on our resume next that's optional so first what we want to do is we want to make a selection inside our data set so we use range a1 a1 is not literal in this case it's just saying pick a cell and then consider that as your starting point in other words a1 as a starting point from there then look inside the current region look inside the data set and then go out and make a special selection find those special cells it's just like going to the go to special dialog box and then what type of special selection do you want to make the type is the blank cells now let's write the formula now if you've not written macros before you this may seem strange formula in R 1 C 1 style R stands for row C stands for column so rather than writing column row a1 b5 we write in the macro formula R 1 C 1 so Row 1 column 1 so over here we're using formula R 1 C 1 style we put an equal sign in to say well what does that equal and then notice that we actually write our formula inside double quotation marks so equals the row go up one row from your starting point so our starting point remember was range a1 so from that row go one row up the minus means go one grow up if I didn't have a - it would go one road down from your starting point but stay in this column so now we've made our selection of the blank cells only in the current region we've written the formula to copy the value from above and now the next line of code is we want to make a copy of all of the cells in there and then paste them as a value so range a1 in the current region select the values and then after we select it what we're doing over here when we say equal range a1 current region value is we've made a copy and that we've pasted them specially as a value what I'm going to do now is I'm going to run you through what's called the debugging so I want to test this out step by step so when I click debug and step into and notice the keyboard shortcut f8 so it first highlights with the yellow font behind the name of the function I'll press f8 now notice that it does skip over the comments so when I press f8 again it's going to run this first line of code here is the next line that will be run while I press f8 again so notice that what it did over here it made an actual selection in the current region of the blank cells and it actually created a formula over here let's bring back that macro over here so now when I run this it's going to change that formula into a value F 8 and then f8 to finish it off so you see how quickly that ran let's come over here and this time I'm going to run the macro by coming up to the Developer tab on the ribbon code macros make the selection of the macro and this time I'm going to say run and bingo there you go so there you've learned a great trick you've learned how you can quickly select all the blank cells use a formula to copy the value from the label directly above map like and then make a copy and paste special so I'll look for you in the next lesson