Create Your Mileage Invoice Template for R&D Effortlessly

Mileage invoice template for R&D

hello this is Randy with Excel for freelancers  and welcome to part 2 of the vehicle expense   tracker in this training we're going to  be adding on to the amazing application   we built last week including the ability  to save maintenance and expenses add in   fuel costs set unlimited reminders create  meter entries and have a complete dashboard   that we built into this so we have  a lot to cover so let's get started all right thanks so much for joining me on this  part two of the vehicle expense tracker in last   week's episode we gave the ability to create this  we designed it from scratch if you did not see it   I will include the links down below however you  can pick up right where we left off from last week   in last week's what we did is we had the ability  to select and display vehicles and we created this   amazing six-part tab system so we can easily view  all of our auto information simply by clicking on   the tabs and also displaying the vehicle when we  select it from the list on the right we've got   tons to cover so we're gonna get right started  before we do I want to make sure that you are   subscribed we do create these videos each and  every week for you every Tuesday as a matter   of fact and I want to make sure you are the first  one to get these alerts when we create them so the   best thing to do is subscribe and of course share  that helps keep these videos for free and the   training I always include a free download you can  check the links down below it's always free but if   you'd like to pick up 100 of these applications  I've got a sale right now for just thirty seven   dollars to give you 100 of my best application  that is an incredible deal people picking it   up so you might want to get that too that saves  you from having to download it and get you all   of course I included a link and a library in that  application so you can link to every single one of   the training videos that's associated with those  applications it is a great feature so I hope if   you do like creating these applications and you  want to turn your passion into profits I have   just the system for you in fact it is the excel  for mentorship program I'm gonna take you on a   12-step process right through all the way from  the defining your applications determining who   what and how you build these all the way up to the  design where we can design your own applications   include projects scope and a flowchart and  everything and then of course the development   we're gonna go cover the complete development  process and then most importantly the deployment   so I can show you how to turn that application put  it on your website create amazing applications in   a full system of reoccurring income from your so  I can't wait if you want it that's interest to you   you want to turn your passion into profits and  we go from anything from concept all the way to   the cash all could the links down below of course  my excel mentor will get you there I hope you'll   join us there all right let's get started on this  project we've got so much to cover last weekend a   full 90-minute ever sowed we covered a lot but  we've got even more to cover what do we need to   do well the first thing I want to do is we have  the ability to add a vehicle and that's great we   created that last week all we need to do is enter  a car here car eight and then just into some plate   numbers and make and model and notes and then we  can save it that's great but how do we when we   make a change how do we save that change right now  there's no way to save that change so how do we do   that for example nothing so when we change here I  want to save that change so we just like back it's   automatically saved how do we do that well that  we can do that through data mapping in fact this   should not be a data validation right we don't  need that data validation it's just a straight   name so I'm gonna remove that data validation  here we really don't need that here because we   have the ability to select vehicles here now if  you have many many vehicles 100 what you would   want to do is create a some type of a vehicle data  validation up here maybe somewhere up here or down   here or sauer maybe even right here so that it's  above then you select the vehicle and then the   vehicle load so that's another way of doing it if  you have like a couple hundred vehicles all you   need to do is create a drop-down list maybe right  here and then select the vehicle and then it would   load up here that would be good then you might not  need this list anymore so there's a few different   ways to do that but what I want to do right now  is I want to focus on the ability to update the   information in our database right just by entering  so how do we do that well we can do that with data   map so just to refresh her what I wonder is I want  to save everything in this table so car 8 when we   created it we created a plate number but we've  created a dupe and we didn't create a picture so   I'll update that code so we don't need that code  so basically what we need to do is when we have a   new we need to make sure that j13 is cleared out  right so let's take an example so we go back into   this we now j13 has a value in it right it should  not right when we add a vehicle right that should   be cleared out notice it's not so all we need to  do this update in that into our developer sheet   visual basic when we go into the vehicle macros we  have add new we have vehicle max at all we need to   do is add J 13 here J 13 1 make sure that's  cleared out because when we add a new money   we want to make sure that there is no picture so  that when user because there's no need to have a   picture there until we actually add one so until  where's anyone so keep that in mind so now when   we click here we click add a vehicle we can make  sure J 13 is cleared out all right glad we got   that done I'll save it so how do we get changes  how do we get changes when we add a car make a   change how do we save those changes well like I  said we can use data mapping it's quite easy and   I'm gonna walk you through that the best way to  start out data mapping is to create a copy so what   I'm gonna do is I'm gonna copy this and then I'm  gonna paste it over just the values it's really   important maybe about 0:06 let's just start with  oh six right here I'm gonna paste the values so   I have a name and a car but I don't need the value  I don't need the value so let's just remove that I   don't need the data here I only need the name and  then what I need here don't need any of that so we   just need that but I also need there's one other  field I need J 13 remember that's our picture so   that's in 13 and J so that's what I want to make  sure if so let's because this is gonna have to be   changed too so I believe that would be here under  T so let's put it under let's put the name under   s picture and then of course T is we're gonna put  the comma so what do I want to do I want to know   what column this is going to be stored in I want  to know is names gonna be in column one plate is   in column two but how do we know that let's just  write it down this is gonna help us column and   then just so we know it's easier to do this and  then I'm gonna drag this over here so I'm gonna   get a column for each one I'm going to center that  then what I'm gonna do is I'm gonna take a picture   of this so I'm gonna use my Snagit editor and I'm  gonna create a picture and okay so now I've got a   picture of it now I'm gonna copy that picture I'm  gonna bring it back into my application and I'm   gonna paste it right down here just temporarily  just is gonna help us understand so what do I   need to do I need to put name name of the car  in column one okay what is next the plate is   in column two you see how I'm doing that it's  really important what is the make the make of   the motto of course is three and then the model  is for the type is five the seats are six notes   of course are seven and then the picture would be  eight pictures all the way down here okay so what   I've done is I've associated every single field  with a column in the database and that's really   important I'm gonna left justify all of these just  so we can clear so they're together and we can see   exactly what we said we've associated this but  now what I need to do is I need to know so when   I make a change just when I make a change to this  name what I need to do is I need to know I know   the world how do I know where the cars stored  because it's an existing car and if we remember   our vehicle database row is 4 so anytime we need  to make a change to a sheet we need three things   three very critical things we need to know what  sheet we're making the change on when you just   know the row and when you should know the column  so so how do we get all three of those components   okay so we know the sheet we know what's going on  here we know the row because it's yours so what is   our missing number missing numbers to column how  do we get the column so I know if I make a change   here I need to get the column and I have placed  the column right here it's calm one so now I need   to do is to find out how what's the difference  between this and this okay so let's take a look   this is column this is calm six now what column is  this right here this is called equals column 16 so   that means if I make a change to column six here  in the current row row six column six how do I get   the column how do I get to calm it's exactly  ten columns away ten columns away is calm one   looking for that one so if I do the same thing  with there if I make a change here if I make a   change here a change here a change here I need  to get the individual column all I need to do is   look in the same row and I'm gonna show you I'm  let's actually you see how we did that how do we   avoid that look how we made a selection we really  don't want to do that what we need to do is add   a little caveat let me do that right now just to  help us so when you have things that don't really   belong that's on selection change notice when we  select a vehicle right then it the vehicle loads   but what I want to do is I only want to use that  to happen if the user selects one single cell so   if target count large greater than 1 then exit  okay that's gonna handle them now it's not gonna   run if we select more than that how does that  work okay let's minimize that now right when I   select multiple nothing happens when I select  the road nothing happens only when I select a   single cell okay so we've situated that if you run  into that problem you don't want things to happen   only when you select one instance so that happens  keep in mind though if you if you have a selection   change like let's say I'm selecting notes this is  considered let's say we've got 4 rows by 3 columns   so this is considered 12 cells 12 cells so if  I make a change this is gonna be a change on   12 different cells so if I have a selection change  that's running it's not gonna work because this is   12 keep that in mind so what I mean is let's say  I want to make a change to f12 right and if I go   into the developers back in here let's reduce this  and so you can see it properly so that means that   if I have a merge cell this is gonna exit out but  I have nothing I have no action on a merge cell so   it's fine now but keep that in mind merge cells so  if you're murdered if you need something happen on   a merged cell and you're merged cell is 4 cells  why you might want to make this 5 okay keep that   in mind the total number of cells in a merge cell  it does affect that okay so back to what we had so   what I want to do is when I make a change here I  want that change to and we know all the dimensions   now we know the column we can remove this we don't  need that anymore so we know the column because   it's exactly 10 columns away right we know this  is column 16 we know this is column 6 so how do   we we look 10 calls over so how do we write that  down well first of all we're basing it on a change   right basing it on a change anywhere from f6 to  age 15 so let's program in that change right now   and that's unchanged event on the vehicle expense  tracker not selection change but actual change so   that would be right here under change and if  not what are the cells that we're focused on   notice that I typed that out really quick that's  called autohotkey I use that it's a free software   to automate my typing okay so we're focused on f6  through age 15 so let's do that right now actually   we need one more we've got to include J 13 right  here actually J 13 I think it's J 13 is one we   have so we do need to focus all the way from will  include column J all the way J down to let's say   row 15 so bf6 through J 15 so let's type that in  now F 6 through J 15 okay so we have that but now   there's something else we need to think about what  is that when we load a vehicle when I click on a   specific vehicle when I click on here well that's  a change to it let me fix this nothing then let's   just write this then and then and if okay we  have the end if that's also automated through   autohotkey okay so if I make a change here right  that's a change notice how there's changes right   but I need to differentiate between the change  when the car loads because that's a change right   the cells are getting changed here and here in  here or to the kind of change where the user   actually makes a change like this right that's  also a change so how do we differentiate between   those two changes well I can use what's called  load a car load notice when the car load this goes   to true I think we program that and notice how  this goes to true we programmed that in so when   the car is loading this is going to go to true and  then back to false see that real quick where does   that come from it comes from our loading macro  and our vehicle here so when we load it let's go   I think it was all that's the first macro here so  be seven sets vehicle to load is true and then it   sets it back to false right here so we know that  if B seven is true then we don't want those on   not the kind of changes so all we need to do is  make sure that b7 is false so let's add that in   so and range b7 dot value must be false equals  false right we can't do it on load we cannot do   that on low we need to make sure that b7 is false  right because when it's true that kind of changes   when it's loading okay so we've covered that what  else do we need to cover well there's more what   else remember this is only I only want to focus  on existing not new cars existing cars right I   want to update because new cars we do we don't  have a database row yet it hasn't been saved to   the database so we don't have a rose so we also  need to make sure that new vehicle b5 is false so   we have to write that in too so let's make sure  b5 is false and range the five dot value equals   false okay so let's write that in let's write  it in on a comment on change of vehicle details   but not on vehicle load and not on new vehicle  okay so we know that so we can understand what's   going on with our code then what do I want to do  then let's dimension let's dimension that let's   mention what's called the vehicle row remember we  need three components vehicle row as long and the   vehicle column vehicle column as long we know the  sheet okay so we not only now we need to get the   vehicle row and the vehicle column what is the  vehicle of the vehicle row is located in the B   4 equals range b4 value that's the vehicle row in  the database vehical ok so we've got that let's   pull that up right now just so we can confirm it  and look it up vehicle row is located in b4 that's   4 remember that's the row that were associated  right here in the vehicle list for in this case   it would be car 1 which is on 4 ok so we need that  now we know the sheet is vehicle this now we need   to call them how are we gonna get that column we  know it you know what the column is because it's   exactly 10 columns over so right that in-vehicle  column is equal to what equal to the cells and   what is it the cells the target road it's the same  row target row right we haven't changed the row   but we have to change a column it is the target  column the existing column plus what nothing plus   ten plus ten dot value that is our database column  database call so now we have the sheet now we have   the row now we have the column now we can write  it up so let's make sure we just want to make sure   that there's one other thing here's what's gonna  happen well I'll let me let me show you the air   so let's write it in a sheet too is our vehicle  list dot cells we're using cells because both the   column and the row are dynamic and variables  what is the row the vehicle grow what is the   column vehicle column dot value what is it equal  it equals the target value okay let's see if it   works and let's save our work and continue on okay  so let me add some information here to this this   one has enough information let's add that's called  this Camry and see if it worked that would be on   car two and of course that's under the make so  we look under that car to here and Camry make is   Camry perfect it worked good but here's an issue  right watch all gonna create a bug what happens if   the user actually changes this it's gonna create  a bug why is this bug you guys run into this bug   a lot you see I got a bug I don't understand what  happened what's the problem here vehicle row all   we need here is vehicle row is five vehicle  column zero oh why is the vehicle calm zero   because there's no there's look when we look at  the row that we changed we changed this one right   here right if we look ten columns over there's  no value here there's no I need a number right   and it's good we don't want a value there because  there's nothing so how do we avoid that we just   ensure although generally what you'll do is you'll  lock these off cells and you'll only keep the ones   open the have values but just in case it's always  good so all we need to do right if we see vehicle   column is zero all we need to do is write if  vehicle column does not equal zero then do   that right that's all we need to do not resets a  bug and now then let's write in then vehicle then   now it's gonna work just fine okay so now let's  reset it and go back in and just make a change to   this in case we do it now there's no issue because  it's zero not only when we change this so now we   can change this to Honda and it works just fine  and notes and type let's just call it sedan and   four okay so seven seats yeah why not okay so  car two here Honda sedan seven that's perfect   that's exactly what we want now let's make sure  the picture got updated I want to make sure that   we're picture so we're gonna add a picture to this  and let's just go and I'll find a picture here I   think it got it under last week's vehicle expense  tracker okay I've got some pictures here let's   open that up okay so now we've got add a picture  here on existing I'm not doing anything else I'm   not saving I'm gonna go car two back to car three  back to car two and it didn't save why not let's   see what did it save it on J 13 right it's not  there so did we make the update on J 13 finally   let's check the column and we see if we know it's  in 13 right let's take a look at this make sure   we have the right place top we've got it in the  wrong we got on 12 all we need to do is drop this   down one more let's try to get add the picture  and put it on here okay now go back and back all   right there we go so we just have the wrong row  now we've got the data mapping all right so if   you have any issues you know just kind of check to  make sure your rows and your columns are all set   up and you'll be fine okay so now any change we  make is gonna save to the database okay I'm glad   we covered that that was really important part and  let's continue on we also want to have the ability   to save expenses if I put in a car number and the  date and I want to save that expense so how do we   do that well let's take a look in this so all we  need to do is take this information and create a   macro so we got an expense type here let's put  in some expense types and add so so we can put   those in let's put them in starting let's create  a I did create a named range earlier so let me   take a look at this although is there's no data in  here so I've got it here between and 825 so let's   do that why don't we just use those so let's  create let's call it repairs let's call this   cleaning poles maybe we have oil and tires okay so  we've created various different expenses for this   and now we notice that here we have expense type  okay so we've got all the data in here repairs and   then we can choose a vendor let's just say Fred's  garage now I want to save this data test repairs   all right so we've got a long one that'll be able  to save this data I want to be able to save it   here in the expense here right here so I want  to put the car number the date so we have I've   got some information here already so how do we get  it in well we just need to find the first row and   then map and I've already actually mapped it I've  done the mapping for us we understand F 19 is the   vehicle H 19 is the date J is the amount again  F 19 h9h 19 J so we understand data mapping so   when we understand the data mapping is very easy  to save let's write some code to do just that into   the visual basic we go and I've got some expense  and fuel macros we can just write it inside this   module I've got the module already set up so why  don't we write it now we can call this sub expense   save because that's all we're doing savings but if  we want we know the expense row row as long and of   course the expense column as long so we need to  know those things we're gonna loop through the   expense columns and of course with sheet 1 that's  the sheet we'll focus on we do want to make sure   that there's at least we do need at least a a  value and we need to make sure there's at least   an expense amount so that's not kind of important  so let's make sure that we have at least a car   which is F 19 and at least a value which is j19  let's make sure those are filled in before we all   let the user save so if dot range F 19 equals  empty or dot range H 19 dr. Evil's empty then   what do we want to do just message box message  box please fill in the required fields exit sub   okay so we now but if we want to let the user know  what to require here's a great way to let him know   all right away on hand it's an easy way just use  conditional formatting so I'm gonna hold down the   control and look at those both of those fields and  I'm gonna add conditional formatting I'm creating   a new rule and I'm gonna use format cells that  only contain and then blanks only blanks gonna   give it a color you can create you know as many  and then we'll just use yellow that's a little   bit too bright so go to more colors and maybe  this yellow here okay so now when it's blank it's   automatically going to be yellow okay so we see  how that works so now that less user know which   fields are required very easily okay continuing  on with our code what else do we need now that   we know that they have filled in the proper values  all we need to do is first we need to know the row   what is the ero let's give it a new row expense  row remember this is every everyone's new so   equals what sheet we're looking on the expenses  list which is she three sheet 3 dot range let's   just choose a nine nine nine nine and and then  Excel up dot Rho K plus 1 that's a first available   row first available spends row now that we have  that we'll all we need to do is again the columns   right we know the sheet we know the row but now we  need to get the column so the columns are dynamic   they're gonna gloop through all the columns and  I believe there's six total columns starting with   the first going to last let's double check that  going into the expense one two three four five   six six total columns so we're gonna loop through  columns one through six and what I'm gonna do is   I'm going to look in sheet 1 look at F nineteen  and put the value at let's say in this world I'm   gonna look in H 19 I'm going to put the value  in the row same thing that's all we're going to   do it's very easy when we do data mapping so we  start our loop for expense column equals one to   six always close our loop next Spence column now  we can write our code what is our code well it's   pretty much easy as going to be sheet three dots  cells what is the row the row of course is the   expense row what does the column expense column  dot value so what is it equal it equals whatever   is we need to range right it equals whatever's  in let's walk through this again even though we   go 42 mapping so often equals what's in every  sheet 1f9 what's in siwon remember this is Row   one so let's do that let's write that out just  so we know it's going to be equals dot range   and what is that range with that range located in  cheek three sheet three that's where the range is   look at it's located in column one cells Bent's  column dot value okay that is the range that's   all we need to do to advise and that right there  so what else do we need to do let's let the user   know now remember we are not we don't have an  add new button on here so that means we need to   clear it out and we need to let the user know so  let's clear out all the fields right now now that   we've added all the information and so let's add  that in as far as to clear the rows what fields   are we clearing well we're basically gonna clear  all these f19 all the ways all the way here so f   19 and they're right here so let's just write it  write it right here we can see that code so let me   write that in right now reduce that so I can see  what cells to clear so we're clearing in dot range   F 19 h19 j19 F 21 H 21 + F 23 23 let's take a look  at that okay F 19 h19 j19 F 21 h21 and F 23 and   we're gonna clear those out so clear not not clear  it clear contents clear contents clear we clear   all the formatting we don't want to do that clear  contents although it's pretty so expected now that   we looked let the user know that it's been saved  message box expense we can use a fade-out message   too but this one's okay for now save okay and then  we're good then that's it that's all we need to do   we end with an end sub so let's save our code copy  this and then we'll sign it to that button expense   save and we'll see what kind of bugs we can find  there's always bugs but that's okay bugs are fun   that because you learned how to fix things you see  I have bugs all the time okay so what we're gonna   do is we're gonna select both of those and then  we're gonna click a sign Macker and then I paste   in this Macker and click OK all right and we're  always good to fill in all the fields especially   when you're saving now I'm gonna save the expense  okay I can't clear this out wide because we've   got we've got to merge cells here notice that H  through J and we have F through J 28 so we need to   make sure that when we're clear when we're placing  values or reading values we can use just the first   sub boom we're clearing out we need to compass  the entire range so let's do that H through J and   F 23 through F 28 so let's just put that in the  code and make sure that that's done so again H 21   through J 21 and then F 23 all the way through J  28 okay now we can run the code now that'll work   let's could save the expense there we go expenses  has been saved we're gonna look in our expense   list and go all the way down here and make sure  that all that information got here good it got   entered properly I will just try one more to make  sure just don't do car 2 and we'll just do 500 and   let's just call this an expense type cleaning  and just call it is expensive okay and we'll   save that expense and then please fill in the  required fields okay actually we need date also   one to actually let's add date and we need we need  Billy J 19 and H 19 so we'll I think all those are   important so let's save those expenses and we need  to add J 19 and so we'll put H 19 and I also want   to put or J 19 right we need both right we need  all three because dates important specially for   the or okay so now we've got it all so now let's  just add conditional formatting onto these all we   need to do again is just do into the conditional  formatting all we need to do is go into the   conditional formatting and then manage the rules  and that will add one more because I'd like to   have comma and then what are we going to add we're  adding of course this one too now we apply it now   they're all three okay so we can save the expenses  our expenses just got saved down here even   though we didn't put Fred's garage so we've got  everything we need now what about fuel with pretty   much the same thing with fuel I need to want to  select a car I want to select the date and amount   again all three of these I want to make these  first three required so conditional formatting   new rule and then format only cells that contain  blanks and then I'm going to get a format now if   you want to find the code that you just used  right we can go into fill effects and actually   use the same color so which is that yellow one  down here so we can do that that'll locate the   even though it's the same color it's not a fade  but it's an easy way to access the same color you   used and now if this is a blank work all right so  basically all we need to do is do the same exact   thing but just we're gonna change different ranges  everything else is gonna be almost the same except   we're gonna have seven fields now why don't we  just copy that and and pretty much make this   so what I'm gonna do is I'm gonna copy this and  then update the fields so this one's going to be   called the sub fuel save another paste in this of  course the sheets different so we need to update   the fuels located on sheet force where overseas  sheet three we need to make sure that it's shaved   sheet for four and then again here this is four  okay so and then we'll change this to fuel has   been saved and I think this is going to be seven  columns right seven so let's take a look at that   expense row for it we're gonna change expense to  fuel so I'm gonna find the word exp and I'm gonna   change it to fuel in only in the selected text and  then I'm gonna replace all okay let's take a look   at that fuel row fuel column that looks good and  then please all the required fields fuel row fuel   column that's good fuel row one again we'll change  these these reduce is not going to be correct now   we need to update those alright so what do we need  to clear out let's look at that I want to clear   out these fields here so let's take a look and  scroll up here in this case it's going to be F 32   so we're going to change it to F 32 we're going to  change it to H 32 and the amount so it just change   these to 32 and then this to 32 they're relatively  the same and then the next row of course is gonna   be 34 F 34 now there's no merge cells here so F  34 and then of course H and also J 34 there's a   note that's a merge field so we got to keep an eye  on that and then the merge field in this case is   going to be F 36 through J 41f 36 through J 41:36  through 41 okay so now we've quickly created a   macro just by copying and pasting and updating it  okay we can you can see that there so now fuel has   been saved and sheet 4 so let's take a look at the  name rank let's just double check it because we're   going 1 through 7 this time and I've already  got so our fuel remember is F H 32 and then   34 and then 36 so let's just take a look at that  make sure Arden 32 32 32 34 34 34 and then 36 so   everything looks good as far as our data mapping  it's just an easier way I'm gonna go over too many   times because you get the idea and you can see how  quickly we can create the macros again this time   fuel safe I'll pop you got Macker and assign that  to the button on sheet 1 here so this one this is   part of a group so I don't want to assign it to  this so I'm gonna hold down the control and click   that little icon now I've selected those two  shapes right click and you can use n or macro   and they pace ctrl-v FuelSave okay again let's  make sure we have the notification that's going   to tell us that the fields required we need to  put in the three fields I got to check these F H   and J 32 let's I don't think I did that here F 32  J 32 H 32 and J 32 okay so those are the required   fields in this case so let's update that alright  now that we have all those we want to make sure   let's run that macro make sure I get that that's  correct so let's put in the date here and let's   put in a mount and the unit let's say we have 36  liters actually unit is leaders and the quantity   would be 36 and the station you know Fred station  Fred owns a lot of property around there okay Fred   is nice and then notes let's write justify that so  it's consistent with the rest of the application   save our work so far okay and now I'm gonna click  Save here let's check our fuel log make sure let's   go down here 39 is gonna be where we need to put  it on so that's where it's gonna be HAP and let's   save the fuel and the fuel has been safe go back  into our fuel car to 143 leaders okay that looks   good 44 54 was that the right and that's why was  that the amount that I added can't remember I hope   so let's just test it again to make sure this  time we're gonna put car 7 on 15 and let's put   the amount of 36 so I can remember that save the  fuel bills been saved go back in the field lock   36 is correct car okay we're looking good on that  now we've done fuel so now everything so now let's   get into the reminders why do I want reminders if  I have to change oil every few months or if I have   to change my tires every year I want to get those  reminders I want to know what I need to do things   and I want to know that so all we needs to do  is one save the reminders to the reminder tables   pretty much just how we have done it with the fuel  and the expenses but in this case what I want to   do is I also want to load in all of the reminders  that are active and that have the results so how   do we do that so that means basically we're going  to save a few information on these are my and   then have them load into this table so we can see  all the reminders but again we can copy and paste   and start out from there that's gonna be a little  bit easier so I'm just gonna click this fuel safe   right here and then actually we can just change  it so what I'm gonna do if I'm to use a separate   module for this because I've got a few other  things and reminders so I'm going to change this   to reminders and then change the cells ingly  reminders save so again we're going to do let's   just change fuel to REM I'm gonna find fuel and  then I'm going to replace it with REM and then   replace all instances in our selected text ok so  now we have reminder row as long reminder columns   long good and now let's change the required fields  what's required for a reminder we need the vehicle   we probably need the name of the reminder and  we need the next date and we need the recurring   status probably they're probably all important  really so let's let's put them all in F 45 H J so   let's make sure though I think they're all kind of  critical so f 45 right we need that we need also H   45 that's important because we need to have a name  otherwise we're not going to know what it is we're   also gonna need j 32 j 32 my mean next day the  next day as possible so we know when to that's   really important so we know when to make it so J  again 45 what else is there we also need to have   more than that so we need to have we need to make  sure that recurring or how often do you want this   F 47 so f 47 let's change that and again also we  need to have statuses probably we need to make   sure it's active so let's go ahead and change that  also copy and paste F 47 so really they're all   they're all important I think so again control  holding down the control assigning conditional   formatting new rule you know how we're gonna do  it by now format only cells that contain blank   values and then we're gonna go into format give  it that same yellow color which we can locate here   in the formatting it's gonna be all down here  and that's going to give us to make sure that   we have assigned everything that we need okay so  now we've got conditional formatting for all the   cells they're all going to turn yellow until we  fill them in day we give it a vehicle we give it   I'll check check and then let's say the next day  would be 3 1 we need to check that and then let's   just say every 90 days and status is gonna be  active so I need to set our hardy set and set that   already I guess Oh data validation again let's  just take a look at that data data validation   here actually it's gonna be just too active and  inactive I guess I had said that I forgot okay   so that's all it's just active in act because I  only want if I don't want to check something or   maybe I want to make it deactivate it I don't want  to reminder for it if it's not active okay so we   have all the information that we need to save it  and it's gonna go of course right in here in our   reminders database and again in this case we're  gonna be looping through last state this one's   going to be controlled by VBA so we don't need  that too much so I'm really gonna loop through   let's say 5 columns so really we want to change  it to 5 so let's go ahead and of course f45 age   45 and then D I've already mapped the data just  to make things go a little bit quicker because   my videos are long enough already let's fix this  issue if it's red we know there's an issue and we   need to space between here okay and just one all  right so that's fine we know that all those are   now required let's expand this so you can see it  and what else do we need to do let's continue on   with the macro so again we're gonna go through  five columns of five columns this time it's not   sheet 5 what it's not sheet 4 it is sheet 5 I  believe let's take a look at that reminders are   sheet 5 so let's update our code to make sure that  it's sheet 5 that we're moving through checking   for the last first available row on sheet 5 sheet  5 is where we're going to place the data and we're   gonna also look for the range in sheet 5 column 1  I'm gonna loop calls 1 through 5 and so that's it   now our next we also probably need to add the row  in there so I'm gonna add that right in here let's   just put in that a different what is it again in  this case it's going to be sheet 5 dot G I'm gonna   have the last date the last day it's going to be  automatic so the last day I'll have VBA take care   of so we don't need app but G we do need so G and  in fact it's not going to be reminder reminder ro   okay dot value equals all we need to do is we  can put in a formula or we can put in now let's   put in a formula equals but on lot we could just  put in reminder row like that that would be fine   but if you're gonna delete rows then we'll just  use a formula equals row so that way it's always   gonna change even if you delete rows it's gonna  be right but if you're not deleting rows then you   can just put in reminder okay so we're putting  in a formula that way it's gonna have a dynamic   grow now all we need to do is clearly contents  it's gonna place all of the data right here not   last date that VBA is gonna handle that but it's  gonna put in the row right here all right so we   have that information here so let's now go ahead  and clear out we know the data is F 45 H 45 we   know that we just have to check here there's no of  course no merge cells so we can clear them out we   don't need to do that so we can clear them out  let's just take a look at that and again let's   go back in here they're already here the cells  are already we already know which ones to clear   out but we gonna add a few more to that so let's  do that clear make sure that's again F 45 H 45 J   45 F 47 47 and J 47 okay I think we've got a few  more though let's just take a look at those and   I also want to clear out the table right we want  to make sure that the table is clear every time   we so I want to what will I'll actually clear out  the table on the load but we'll make we'll make it   so really only those five and then what I'm gonna  do is once I save it I want to reload this table   so I'll clear the table out and reload the data  in another macro okay we'll call that reminders   load so we've cleared out all the contents of it I  think forty this should be 47 now 41 that's it so   all those are good to go now so the three those  five cells will be cleared out and that's good   what I will do is I want to add another macro here  and we're going to call it reminder load reminders ok so we can write that in just a moment so sub  reminder load okay cuz that's important don't   little load all the reminders what reminders  do all on the load I want to know which ones   are active and I want to know which ones have a  next date that's less than the current day I want   to know the let less than the current day less  than equals today right I want to know all the   reminders not the ones that are past but ones  that that are either less than or equal today   so we only know the current reminders in this case  we have the next day so there wouldn't beginning   to look let's update these existing so let's just  120 121 and 122 ok so what do we want to do is we   want to have the frequency and the last day we can  clear those out so let's say so basically what I   want to do is I'll every 90 days I want to remind  me so that if the next day of 20 we know that the   next day after that's gonna be three months so  basically I want to load that data in here and   then I want to understand check for reminders so  let's first finish this macro by assigning it to   the button here so I'm going to right click assign  the macro and we're going to say called reminders   Save so let's now save our work here and I'm gonna  double check that Coach make sure we're looking   save reminders okay let's run that code and just  see if we run into any issues reminder has been   saved okay let's go into the reminders and see car  3 oil check active that looks pretty good that's   exactly what we want seven last date is empty okay  great let's load in those macros so we want one   more I want to load in those so what I want to  do is I want to take all of these and I want to   bring them through a filter I'm gonna know which  ones are active and I want to know which ones are   before or equal to the current date so I'm going  to use this criteria active and before or equal to   the today so that's gonna help us and then I want  the results I want to place them right here then   I'm gonna take those results and I'm gonna bring  them right into this table right here alright so   let's write some code to do just that so again  we're gonna call this reminders load and we're   gonna primarily focused with sheet one so sheet  with sheet and now again we're gonna let's say   we want to know the last row dimension the last  row as long you need to know the last row of the   reminder so that's not fun on g1 but we can focus  on that so what are we focused on here let's just   run an advanced filter automatically and bring  that data and we've already have our criteria   set so the last row is going to be equal to sheet  5 that's all reminders dot range a then nine nine   nine nine dot and Axela that's going to be our  last row dot row last row okay so now that we have   our last where we can run our advanced filters  but I also want to also make sure we clear the   contents of that and I want to clear again I want  to clear this table too so let's clear that out   e-50 through j59 should be cleared out so dot  range e-50 through j59 later the content clear   contents so we've done that and also sometimes  I want to clear the contents usually VBA will   do that but I like to do it sheet v l4 through  are let's clear those out let's clear out any   previous results dot sheet v dot range l4 through  R and then of course all the way down R and then   let's just say nine I know I'm gonna clear those  dot clear contents clear results remember vba   tends to do this but i just like to do it just in  case so now we're ready to run our advanced filter   so sheet v dot range what does the range we'll  remember one include the headers a3 a3 is gonna   be our header all the way over to G and what and  the last row dot advanced filter we're gonna copy   to new location and what's the criteria criteria  sheet v dot range and it is going to be these I   three through J for I 3 through J for dot not done  that's it copy to what are we gonna copy to range   : equals you forgot this criteria range : equals  we can make sure we add that in okay sheet 5 copy   to range where we copying it to again sheet 5 .  range and what's the range again L 3 through R 3   L 3 through R 3 that's the range then of course we  want unique : equals true or false I guess doesn't   matter in this case okay so we have all that there  we go now we've got let's just take a look at   that sheet 5 a 3 through Gina last row advanced  filter' copying it to another location criteria   range sheet for I 3 through J 4 that looks correct  and we also what copy to range equals sheet 5 l 3   through R 3 ok so we have that now all we need  to do is bring in the results very very simple   so how do we do that once we've run our venice  filter dot range again room focused on sheet 1   again be 50 that is our that is where our results  will go of our reminders through what is it going   to be J 59 all the columns match up so it's really  easy value equals what equals sheet v dot range   and what's the range our results are located in  l3 through we only have the maximum roast so we   have 10 maximum rows so let's just go through Q  or R we don't need the row so we can just do Q   in this case Q 12 okay so that's all dot value so  that's gonna copy over reminders all right that's   perfect okay so that's good and I don't think  we need anything else in this macro we're gonna   run it automatically runs reminder load I also  you know what I want to run this I also want to   run this on tab as soon as we click in the tabs I  want to run it too so let's go into the tab macro   and scroll down to reminders that's the one we did  last week I'm gonna add it right in here I'm gonna   put it right there so that way every time we click  the tab is going to also load let's check for any   bugs on that click on the reminders here and then  we need to clear contents and that looks good all   right so now everything loaded in and although  we didn't include the headers we don't need that   obviously so what we don't want to do is just  make sure that we actually this has to be - ok   let's update the code our advanced filter should  be 2 L 3 - R - we don't need that all right there   we go now it's gonna be right now let's try it run  that again that looks good there and let's take a   look that looks good there ok let's just uh format  this everything looks really nice ok we can Center   that and then let's take a look and then we'll  Center this ah this is on the left justify ok so   now we have our reminders and but now what I want  to do is I want to check for reminders and then I   also want to update that so let's finish off our  reminders and then we're gonna move into the meter   and then we're going to finish off with just some  nice little graphs ok so basically what I want to   do is I want to check for any reminders that are  before the current date right before the current   date and I need to know if they are I need to put  a little pop-up and then if if it says it's been   completed then I'm gonna schedule the next date  for 90 days from this so the next one's gonna be   90 days so how do we do that well just write a  little bit of macro it's a quite a small macro   let's go into our VBA and write that Mac and I'll  expand this a little bit and so we'll call this   sub check for reminders and then we're gonna  primarily focused on sheet 5 in this one so we   need a few rows dimension and let's move this up  a little bit here to mention the last row as long   also I need the results row result row as long  and also the last result row because I'm gonna   do it another advanced filter very similar this  last result row as long okay so with don't need   two A's there okay so with sheet 5 what we can  do again we're gonna we need the last row in this   case with sheet 5 so let's get the last row just  like we did here so it's gonna be no different   except we don't need sheet 5 so let's copy this  in fact we can copy all everything here we did   pretty much so everything right here just get rid  of that and I'm gonna copy that down here and now   I'm just gonna get rid of the sheet 5 references  because we're using with sheet 5 in this case so   it's not necessary and again so we're gonna run  through our advanced filter I also want to know   what the active ones are and I also want to  know everything in the past right so that's   before the current date so now that we have our  advanced filter we're gonna run through the loop   but first I want to double check to see if there  are any results so last result row is equal to   in this case we're going to use l dot range l and  then we'll just go to nine nine nine and then end   Excel app dot and X Allah dot row okay let's get  you this our last result row what is that just so   we're on the same page we're looking at that what  does the last result row it's gonna be this one   so I'm easy I want to know the last result grow  in this case it's six so I'm gonna loop all the   way from four to six check for reminders and this  is where this rows gonna play into the factor so   we need that okay so now that we have the last  result row what else do we need to do I'm gonna   run a little check if the last that's actually  result row is less than three then go to no   results okay so that's going to go all the way to  the bottom and we'll just put down here no results   in fact let's do a little message but let's do  exit so I've will exit out of that no results   and then we'll put the call in and then down below  let's let the user know message box there are no   current reminders that way we know at least the  codes been running so okay so everything else is   gonna be up in here and this everything is it's  gonna end here assuming that there are reminders   okay so assuming that there are current Myers  what do we want to do with them I want to run   a loop for the results row equals again starting  at three all the way to the last result row and   close our loop next results results row okay so  now we've got the loop so what do I want to do   inside this loop I want to run a check if message  box you have scheduled or they should we put a   scheduled reminder for what is reminder : and  what is that reminder and then we put in quotes   and what is that I want to post a reminder what  is that reminder I want to put this whatever's in   EM I'm gonna tell the user you have a oil change  so let's put that in there M and dot range m and   what row will it's gonna be the results row and  the results row dot value so then what else so   we know it's m and then what I want to put in and  space for and what is the date so the date in this   case would be what's located what is the data  that's scheduled let's take the drop this down   here the date of course is located in n so let's  write that down dot range and and the results bro okay but I don't want just the day it's gonna  mix may not show me the format let me format that   date just so we know format I want to make sure  the format the date is in the format that I want   in this case let's put it in as mm / DD / yyy you  can use any format you want close that and then   what do I want to do then I want and we got to put  an end here okay and what else and the format so   now we have the format now we've got that what  else do we want to and let's put a new line cbcr   and put a question in let the user know has this  been completed okay let's go to the next line so   we can see has this been completed question mark  okay now we can say comma V be yes/no give the   user the option okay now close it and then we can  put a title in there vehical reminder actually we   need a car number in there too let's add a car  number here so for let's let's add that and dot   range the car number is located in l l and the  result row okay good now we got the car number   in there and then of course we got to do the  and again on the sis - on and then the date   and okay so that's gonna cover it so for the car  on whatever date then we give the user the option   here down here has this been completed by but by  yes no vehicle reminder okay equals VB yes then   what do we want to do then I want to do something  I want to put the next in switch then I want to   put the last date in there as the next day and I  want to update the next date but I want to do it   I'm gonna use this row because I'm gonna update  the original data so that rows important so then   what we're gonna do is we're gonna update I want  to update the last day the first thing I want to   do is the last day gonna put that current date in  there okay so let's just put the current date dot   range F right after we focus on F and what is  the row row of course located an R there's also   dot range R right that's where Rho is R and the  results row dot very so that's a row this is all   row right here dot ransomed it so this is our  column so what do we want to put there well I   want to put there the date equals the day let's  just put the current gain in their current date   now what do we need to do now I need to update the  next date for basically whatever the current data   is plus whatever the frequency and so we can do  that with this line of code here dot range see now   we're focused on C which is the next day because  we're gonna update C again also and the using the   same row of course the same row so we can just  copy that and paste that right in here and don't   forget put the + sign in here okay so now we  have the next day what are we updating it with   we're updating with whatever is the current date  plus whatever's in d column D so equals equals   whatever's in column D just copy and paste that  it's lost the current date plus the current date   date okay so set the next date that's it that's  all we really need to do that sets all reminders   ok so let's take a look at that and let's run  through the code there and see if we how we're   gonna see if we got any bugs and let's say okay  we'll save our work and let's just run through   that and let's run through that and then in values  dot row okay obviously we need to update the last   row last row equals forgot that okay I'm typing  too quick and then results row we need dot that   should be results not result these variables and  I always use the wrong one so bear with me here   well I work through these okay you have a reminder  scheduled oil change for car one on one twenty has   this been completed yes now you see that last  date got in there as it's been completed yes no   then yes okay good so that's exactly what we did  notice that we have last date on here and now also   the next dates completed so now when we go back  into our reminders and we click now let's assign   the macro to this check for minders right click  sign macros again reminders check for reminders   here now it should just you have a scheduler minor  ok good we have ok ok ok very good we just gotta   update these I gotta update the criteria let's  take a look at that criteria should be I 2 through   J 3 let's make that adjustment there real quick I  2 through J 3 in this macro and of course I 2 and   J 3 in the disc macro so there we go now we can  get it correct now let's take a look at it and run   it we just run it once let's run it again go into  there and then what we'll do is we'll check for   reminders here click on car one that works ok just  one and then what I'm gonna load it now we have no   reminders good we've all been checked let's just  change one of those dates double-check it we have   them all let's change two of them - let's just  say - one which is in the past and - one and we   can get rid of those ok so let's update that data  and make sure we get one I want to load the two   reminders in so let's load those I'll load them  in the next date notice the next date check for   reminders we have a check would you like to this  has been quickly let's say no on one and yes on   the other and I guess we shouldn't load these  again right shouldn't we load these again let's   run that one more time so what I'm gonna do is we  have the check for - but let's run the reminders   load after we do this I think that makes a lot  more sense ok so now let's do that one more time   check for reminders let's just say no on this  there we go that's what I want the updated list   so now we have a list of all updated reminders ok  great continue our minders are working good let's   focus on this save meaning the meter tracking this  is going