Learn How To Create Your Own Account Reconciliation Application In Excel From Scratch +FREE Download

hello this is randy with excel for freelancers and welcome to the account reconciliation manager in this week's training i'm going to show you how you can create your very own account reconciliation manager absolutely from scratch and that's going to include everything from up until any type of account any type of transaction you're going to be able to select all or select some of them and reconcile your own accounts it's going to be an incredible training i cannot wait so let's get started all right thanks so much for joining me i got an incredible training this week the account reconciliation manager whether you have a bank account or credit card charge accounts or any type of account i am going to show you how you can quickly and easily not only design software around how to reconcile those but how to create those for any type of account quickly and easily and automatically if something gets in balance it is going to show just like that so also you're going to be able to add interest you're going to add charges and service charges and that's going to be whether it's for a type of credit card or type of bank or any other type of account we're going to be able to show you how to do that how to reconcile books very very important when you are trying to keep proper accounting books reconciliation is the way to do it and today i'm going to show you how to do it we're also going to show you how to navigate past and we're going to do it all from scratch we're going to be designing this from scratch i'm going to show you how to add new ones and also we're going to show you how to automatically save existing ones and load those existing ones simply with a search we'll be able to load in previous reconciliations just like this and we're going to do it all from scratch as a sample workbook i do bring these trainings to you each and every tuesday absolutely free i love your support there's so many great ways you can support this channel if you like this content one simply like and share that's a great way to do that sharing these videos liking them comment below that really helps another way is to join our patreon account we've got a brand new patreon account with tons of incredible benefits such as downloadable pdf code books automatically updated trainings and workbooks so for example there may be some features that i leave out or that i add on later based on your suggestions those updated trainings those updated workbooks are all available on patreon that includes any type of feature that you may want to see i'll include that any type of fix that you might need or we might need on a workbook or any type you want me to focus on a particular part of workweek i do all that with the feature focus and fix inside our patreon so hope we'll get started on that that just starts at just three dollars a month and it's a fantastic way to support us once we get to 500 members we're going to be a free course for just those members i'm going to be sharing that entire course brand new course only for our patreon members so that's our goal 500 members okay great let's get started on this don't forget to subscribe and click that notification icon bell that'll alert you when we got these changes all right this is the sample so basically what i'm going to be doing in this is i'm going to creating it from scratch so based on this sample we're going to create have the ability to have an account the ending balance statement date search id beginning balance and then if there's any cash back or something now keep in mind that if the account type is like a credit card it would be cash back or credits and interest paid where if the account type is a checking account it would be interest earned and services but i want to design all this from scratch so what we're going to do is we're going to close out this this is a sample we don't need to save our work and i'm going to start right here now this is the blank workbook this is the page that we're going to be designing it all from scratch every step is going to be with you every step line of code every formula every feature i do have some data and some foundational things that i want to go through with you but let's get to designing this one first because that's what i want to do because i want to show you not only give you these great applications but show you how you can create them yourself so what we're going to do is we're going to keep those first two columns for admin and what you'll want to do is you want to hide those columns so we're going to color those gray and that's a really great way to start because you've got two columns dedicated for admin and then what i'll do is i'll save this top row probably all the way till about say q this is going to be our heading row so i'm going to format that cell i'm going to give it a fill is a great one training for if you're just getting into vba or you have a little bit this one i'm going to walk you step by step so sometimes we do these things if there's a simpler application like this we can do them from scratch other times what we'll do is we will generally create them and then sometimes i'll go over the code step by step with you it really depends on the size of the application the type of the training we go through so i've got i got something for everybody in these trainings okay so what i'm doing right now is i'm just giving it a fade effect the top and the second line over here and then i'm just going to give it a general color and that's generally what i do throughout just kind of keep an eye on it and we'll go probably down i'm going to go down a lot of rows because we got a lot of data possibly so we'll go down to about q let's say 300 it's fine for now i'm just going to give it our baseline color this is going to be our baseline this turquoise color if you want to get this exact color scheme let's take a look inside we're using this one we are using solstice so this one's called solstice if you have that that's the one we're using you can use anything you want okay so let's give it a heading right now we're going to call this let's call it a count just as you saw inside the sample account reconciliation hopefully i'll spell it right for a change manager okay obviously we're going to need a bigger title that reconciliation i probably spelled it wrong all right so let's take a look i'm going to give it a font of 30 and then what i want to do is also i want to give it a specific font something that's common i use this arial rounded a lot and then we'll go in the middle okay so that looks pretty good that's going to cover it i try to keep these consistently and i'll give it some icons on a little bit later okay what we want to do is we want to start out some fields and we're going to have a few fields here starting in two what i want to do is i want to let's just start on the fields here that's a little bit easier more clear so i'm going to merge and send it this one because i want column d is going to be for the selection as you saw and column e is going to be for the transaction data and a few other things but what i want to do is i want to have that account drop down list here so what i'm going to do in row three actually let's give it a little bit more space row three what i'll do is i'll merge and center that so i'm going to merge the center of that and then let's do the right justify and we're going to call this the select account so we want a drop down list of accounts select accounts and i've got an admin with a few accounts listed so we're going to select a specific account and then we'll have a field here that'll be for the account then i want to have an ending balance ending balance as you saw on the sample and then lastly what i want to have is a statement data we want to know this date of that statement that's really important because that date of that statement is going to determine what transactions loads we may not want transactions after that statement date but we do want transactions before that statement date okay and here what i want is i want to know what reconcile number if we're loading a previously saved reconcile i want to know what id that is reconcile right so let's do a reconcile id and then i want that id up here probably let's put it here under actually let's put it here i'm going to copy paste it i'll put it in j that's going to give it why is that because i'm going to use this column as the id because it's a smaller column so this is where i want the id so we put something like 1 or whatever here so that's where i want and i'll left justify so that's the currently selected or if it's a new idea we'll do that okay so we have a statement date here and then also i want to skip one row have one row for space and row five what i want to do is i want to put basically it's going to be based on an account type so let's just put in a temporary something called cash we're going to change that cash back okay it'll change in a moment because it's going to be dynamic okay and dynamic based on the account type remember bank accounts and credit card accounts are handled very differently so we want different text based on that and i'll put another one just put fees in here i'm going to put something temporary it's going to be a formula and then i'll just uh right justify those here and then we're going to have the let's right justify and then here what i want to have is i want to have the charge in this case the charge amount what is that amount charge amount and then here i'm going to have the date so we're just going to put the date of whatever that charge is so that means if you've got cash back or cart or something on your account you know when you get your bank account there's a certain amount of fees or maybe you earned interest i'm going to put all those transactions there and then again i want an amount here and then the date here so i like the way that that looks and uh so now what i'm going to do is i'm just going to hold down the control and let's merge and center these so what i'm going to do is i'm going to copy i'm going to paste special and then i'll paste the formats there all right and then i'm going to merge and center these just like we did above and then i'm going to right justify just like we did okay so i've got that one i like that and then i'm just going to hold down the control and then i'm going to format these i'm going to give it a border here we're going to use the same consistent color here so that border is going to be this darker turquoise here then i'm going to surround it with this thin line and on the right side we're going to put that dotted line and then likewise i'm going to do something a little bit different here these are the fields that are going to be the input fields so in here i want something i want to call these white so we'll give it that white color the border on these is going to format those cells and then the border is going to be that same color but on the top right and bottom okay so we've got our fields now these are all going to be dates so i can format those as a short date that'll be sufficient and then i'm going to right justify these fields holding down the control right justify those so we've got to reconcile id our form server so what we'll do is we're going to skip a row and then we're going to put in row 9 we're going to put our main header we're going to call this account transactions because we want all of our transactions to list here based on whatever account we've selected here i'm going to merge and center that all the way across here and then we're going to have our subheading here so our individual columns in this case we're going to have a selection here in column d select all just as you saw in the sample i'm going to have a transaction date here i want the description here and this one we're going to do a merging center because i want the description to be long so i'm going to merge and center the description all the way over here like that and then next up what i want is the check or reference number and i've got some sample data we'll go over the moments reference number and then next up i want the debit amount debit amount and we want the credit amount that will increase or decrease based on the account type credit amount if it's a credit card that credit amount will increase if it's a check-in or bank account that debit card is going to be the one to increase so let's go ahead and give it some formats i'm going to right click and format those cells we're going to give it a fill we're going to use the same fill effects that we did on the upper to keep the theme consistent so i'm going to go with this medium dark to medium light here and then i'm going to paste that down okay then i'm going to click format those cells and then this one i'll do a border and also just fill effects the fill effects are going to be the secondary color which is the lighter fill effects right here and then we're going to click ok and now i'll just give it a border all the way around that'll be sufficient all the way around we can actually give it the color if you want you can give it a black or we can use the same border color so if we use the border color here the same border color that we're using all the way around in the inside as well okay so saving our work so we do so far we've got our dates so now what i want to do is i'm going to skip and i'm going to put some summaries in here here what i want to be able to do is i want to be able to search for an id search as you saw there's a search id so we can search for previously created reconciliations and also here i want the beginning balance i want to know what the beginning balance of that is usually on our statements it comes with a beginning balance and we can write that in so the beginning balance there also i want to know the cash back in fact what i'm going to do is whatever is in here equals whatever's in here because this is going to be a formula i want to put here cash back and i also want to know the fees so the next one is going to be equal to the fees this text because this text cash back is going to change and we'll go over some formulas just in a minute it's going to change remember based on a bank account now what do you mean by account in the admin let's take a look very very simple all we have is simply a list of accounts and then we have the type here we can use the drop down list i'll put a drop down list here bank or credit card right because they're handled a little bit differently so we want to make sure that we differentiate between the two i've also got a named range if we under formulas in name manager i've got a few named range to help us out we have account name now that account name is going to be a dynamic named range using the offset formula and it's going to be based on those names and i also have an account type again another dynamic named range but this one's going to be on the type and that's going to help us determine the selected type when we select a specific account so it is this name manager this account name here that we're going to be using in the drop down list here so if i want to select that account type i want to know the name of that account so we go into data data validation here and we create a list listing and what type we're going to use f3 we're going to select it from our list and this media account name so we're going to click ok so now we can select between whether it's the check-in or all the accounts associated we're going to need that that's very important so what else do we have we're going to i want to insert a select all or check box here we can do that using the insert i'm going to insert a symbol now let's take a look at this whether it's an unchecked or check box vba is going to handle that but if we take a look at there into the wingdings font this is character 168 we're going to alternate between this unselected box and we're going to alternate between the selected box which is character 254 so between 254 which is selected 168 which is unselected we're going to be alternating with that we'll start with that and we're going to close that okay so we want to do the same thing here but we want to make sure that the font is the same throughout so what i'm going to center that and then i'm going to go all the way down let's just say to a lot of translation we'll go all the way down to let's go with d1000 there go all the way down there that's about as far as i want to go and then i want to make sure that that font is the wingdings font because only that column that is going to take on that check mark so i'm going to hold down the shift and then select the wingdings i'm going to enter that so now that they're all wingdings and that's what i want there okay okay so now that we've got the transaction dude i'm going to center these here making sure they're centered i'm going to bold them and then this one as well it should be bolded as their header okay so as we enter we're going to put some conditional formatting here that's going to help us saving our work so continuing on so here we have the search id we have the beginning balance we have the cashback these two are going to be user filled so i'm going to color those white because the user's going to actually fill them these two are not so for all of these and so else we want fees then what do we want i want to put that ending balance what is that ending balance because it's going to be our summary right here then once we have the ending balance i also want to put the clear balance what is that clear balance that's going to be a formula that determines what is our clear bounce based on the selected transactions which one we're selecting after that i want to know the difference what is the difference between the ending balance and the clear balance very important when we're reconciling statement do so what is that difference and we're going to put that difference in right here okay so i'm going to right justify these and we're going to also format those cells all the way around here and then we'll give it a just a border we'll use that same color that we've been using that dark green and all the outline and all the inside but the inside here what i'm going to do is give it that dotted line okay i like that this one i want a little bit different i'm going to make sure that this is format cells i'm going to give this one a little bit thicker border on the top and on the bottom and then probably the left and the right just so we can differentiate that's very important and i'm going to make sure that it is bold so good okay we're good to that we'll add some conditional formatting onto there so cashback this is simply going to be whatever is located here the amount the fees are going to be at whatever is located here equals the amount here and then also what i want to know is the ending balance now the ending balance is going to be equal to whatever we enter here that's the ending bounce that years are going to enter beginning balance and then the clear balance that's going to be a formula we'll go over that in a moment and then the difference basically is the difference between the ending balance and the clear balance so that's just a formula so all but i do want to do is round that out so we'll go over this formula soon and then we're gonna enter the search id so that's pretty much it for that now all we need to do is add some button sets and some conditional formatting and we're going to be good to go okay but here let's see we got to add some more information here so what do we want to add as far as the admin area and the columns there in that area i want to know if the reconcile is being loaded or not so it's going to be a true or false boolean so so we're going to put that there i want to know if it's being loaded i also want to know what the account type is i want to know is it a bank account type we can use a formula for that so how do we gonna know that well that of course is based on whatever has been selected so we're going to start with iferror and then we're going to index now remember we have a named range based on that account type so that's exactly what i want what is that account type that's what we're indexing we're going to base it on the match and we're going to match what row is it we're going to match to see when this f here is we're going to match we're looking at basically f3 and we're looking up in the array of the account names let's type up here so we can see it up here account names right we're going to look for that and we want an exact match okay we want the column is going to be one because they were just the account types with single column and there we go that's the index but what if there's an error if there's an error i just want to show blank okay so that way we have bank account if it's showing and if we select we said that we have a credit card okay so now we've differentiated between which is exactly what i want and the next one in four i want to know what row we've selected right if i have the recon row we have a database here with two databases and we're going to go over those now the first database is called the transactions now this is all of our transactions we've got transaction date we have a transaction account we have a description we have a check or reference number we have the debit amount credit amount it's either or but it's not both we have whether it's been cleared or not right whether it's been cleared we have the recurring id what id is associated with that remember when the reconcile if we save it it's going to be one two or three as we save this we can that way we can go back to a previous reconcile and make changes or just look at it okay so we want to save we want to know what reconcile that was and we also want to know what row what is the row of the reconcile is it row 11 row 12 row 13 because when it comes back i wanted to come back in the exact same row that we saved it so that's going to be very important so we want to know what row i also want to know what row of the database that is this current row this is row 6 7 8 so we're going to use that and we're going to use the formula to do that so we've got our database and we also have our reconciled database right we'll reconcile one we have an account right we have a beginning balance if there's any an ending balance then a statement date and all that's going to get saved so when i save that ending balance and that statement date it is going to get saved directly inside this database here and so what we want to do is we want to keep track of the reconcile ids now i've created a named range for that under the formulas the name manager and the account reconcile id or rec id and again i've used an offset formula here if we zoom in on this reconcile starting in a2 we're starting in that header row but we're moving one row down not skipping any columns we're going to count based on all of the rows starting in the header row but we're also subtracting 1 because we don't want to include the header row we do want to include the header row because it prevents errors and when there's no data so that's important okay so we understand that we have that recurring id so if we know that and we want to know what row if i want to know what row one is or i want to know what row on two is how do we going to get that well it is that formula that we're going to place right in here so in four i want the reconcile row and what is that row so i'm going to look right here inside k2 i'm going to determine that using a formula so this is going to be equals if air if there's an error i'm going to match and what are we matching we're matching we're looking up this value and what is the array that we're looking at we're going to look up that ray that we're just talking about which is the ring style id and i want exact match okay but i want the row i don't want to just found it so i don't want just to find it so i want to add 2 onto that because that's going to get us our row because our first one starts in 3. if there's an error i'm going to show blank okay so that way number one is on row three number two is on row four so that's working just fine okay so what we're gonna do is i'm gonna left justify these and i'm gonna right justify these here and then what we do is we're gonna give a color probably all the way up to let's say we'll give these ones up until five a certain color because there's some things that i'm going to put in i don't want that black font and then we'll give it this color let's just say this orange color here and then what i want to do is i want to have some information here so let's go ahead and all the way down here add some rows so what do i want in here i want to know the next id what is the next reconciliation next recon id we can use an equals if error there's an error i want to use the max formula i'm going to max the reconcile id but i don't want the maximum i want the max plus one that's going to tell us what our next idea is if there's an error why would there be an error well there'd be an error if there was no date at all so if there's an error i'll just want to set it to one that's going to be our first but there's no error and our next one is going to be four okay so what do i want in here i want to make sure that when we load up i want to make sure that they have the account they have a statement date i want to make sure that we have everything we need in here when we load it just in case we do so at least i want to make sure that we have a slight account and a statement date here i want to know how many fields are filled out here so in case we want to change the required fields it's very easy that's one way to do it required fields and we can use account a equals count a and so basically i just want to count holding down the control here and here it's going to return one right as we fill those in and so we fill those in 456 these are amounts so i'm going to hold down the control here and here and these are all amounts i'm going to sign those amounts same thing right here these are all amounts here and i'm going to do the same thing here okay so now we have amounts okay so i want to know how many fields and this is the date so if we put in 8 15 we see the date all right and i like to left justify these here saving our work so now what i want to do is i want to make this dynamic this text dynamic based on the account type well let's continue with these i want to add a few more i want to know the required fields for the service charge right if there's a service charge being added here here here i want to make sure that the user has filled in this and this and this and i don't want them to leave any blank so what i want to do is i want to count the number of fields that have text and i want to make sure that it's three before we actually save it so we're just going to call this service charge fields and again we're going to do the same thing equals count a or counting all the text and basically holding down the control here and just selecting these three fields okay we're just going to make sure that now it's zero which is fine but as soon as we add some text it's going to go to one okay i'm going to do the same thing here but this one we're going to call this let's call this interest fields and i want to know interest because it's going to be either interest paid or interest costed so equals count day i'm going to count those fields just a quick way of making sure that the user has filled in the required fields as in the vba we could say if f7 is blank if h7 is blank if j7 is blank right then do this but it's much easier to say is this 3 right as soon as this is 3 right we put an amount here and put in a date here as soon as this is 3 we know that it is automatically have the required field so that's an e quick way to tell saving our work so that's it that's all fields so i'm going to put one more in here i want to know whether we're going to hide the transactions based on a certain date so that's going to be a check box so i'm going to insert in this case i want to insert a form control and i'm going to put a form control right here and i'm going to put some text right here and i want to know as you may have seen in the sample i want to know hide transactions after the statement end date hide this is going to be an option transactions after the statement and date i'll make this bigger so that's what we want to do so i want to know whether we should hide the transactions based on after this date right so maybe we don't want to see any transactions after the statement date we have an option to do that and i'm put this format that control and i'm going to put the cell link here based on b9 and click ok all right so we have that there let's give that a little bit of a color the same thing and we're going to call this hide transactions okay and then put a question right there okay so as soon as this gets checked it goes to true that's what i want very good all right so we've got the basic part of it we're going to put in some buttons we're going to put some conditional formatting now what i want to do is i want to put in some conditional formatting based on some information here just a few different rules and i want to add some dates let's format those dates again let's go down to e 1000 and then format all those dates accordingly i'm going to format all of these just holding down the shift and give it that short date so they're all the same i like that that's going to be just fine so now we have that and i guess we could center that let's just go all the way down here and center that all the way down okay generally i'd never go down this far but you don't go down because it does take up space but there's a small application generally you only want to go down to the limit great so we've got a description and we've got to check them out we've got everything we need let's uh we're adding some conditional formatting so what i'm going to do is just going to highlight some cells here go down to condition formatting we're going to do a new rule okay i want to base the formula and i want basically a few different formulas first one i want to do is i'm going to basically color the odd and even rows based on a condition so it's going to be based on two conditions actually so it's going to be equals and and what are the two conditions the first condition is i want to make sure that e and any row starting at 11 any row starting at 11 so we want to get rid of the absolute awesome does not equal empty that's going to be the first and what is the second condition i want it going to be be based on odd row so odd rows is going to be one the mod of one we can get rid of that equals okay so that's two conditions so for odd rows what do i want to cover them i want to give it basically this background color but a little bit lighter so i'm going to format those i'm going to fill it here's that background color but i'm going to go into more colors i'm just going to make it a little bit lighter clicking ok clicking okay okay that's one rule now what i'm going to do is i'm going to copy that and i'm going to click ok and then what i'm going to do is conditional go back into conditional formatting manage the rules actually and what we want to do is we want to base it back on a lot of rows not just up to 16 so we'll go to 9999 okay click apply so that's going to apply it to everyone now we're going to get a new rule i'm going to form this and i'm going to paste that same rule in there except i'm going to change it to even rows now in this type of format and then i'm going to go into again selecting that color and then more colors i'm going to give it a very light color nearly to white okay and then click ok and then okay again and we'll just double check the range and we're going to make sure that this is also the same range and then give it just a large range it's fine sufficient clicking apply okay and that's what exactly what i want i'm going to do one more so i'm going to do a new rule again using a formula and what we're going to do is we're going to base it in this case on just a single condition so equals in this case e 11 getting rid of that absolute 411 so it's every row does not equal empty basically what i want to do is i want to give it some dotted lines in the middle so i'm going to give it a border and i'm going to choose this this turquoise color here and i'll go a little bit darker here maybe this one and then i'm going to use a dotted line here and here and clicking ok but i don't want to apply this to the outside borders i only want to apply this to the inside so what is that range going to be it's going to be starting on e11 and i want it to go only to i not all the way to the end so i and then the last row okay so it's gonna based on that so then nine nine nine nine nine nine is enough and then apply okay so that's that's exactly what i want so as we add information here you see we got the nice conditional formatting we've got that dotted row so that's exactly what i want to give that so the conditional formatting is done we're good with that let's give it a little bit of a button set and then we can start adding in some vba so i'm going to insert here and basically some shapes just some basic shape button sets and i'm going to move over to the right here and we're going to add them in right here and basically i just need three buttons save new and delete that's going to be sufficient so here we're going to give it the style the basically the style of our form is here i'm going to call this save yeah we can go capitals i think we'll go capitals on this as opposed to the sample and then what i'm going to do is i'm going to format this one just the way i like it so we're going into the size and properties and then go into the text box here and then we'll do .03 on the left but the right is the most important because i'm going to right justify that i don't need anything for the top and i don't need anything for the bottom that's sufficient enough and so then what i want to do is i'm going to go in here and then right justify it in the middle and that's just the way i like it there okay so we're good on that all right once we have our save button the way you're like what i'm do is i'm going to duplicate that twice and i want to create ones for new and for delete so i'm going to put that down here so we're going to call this new and we're going to call this delete and that's going to be for be able to delete those in case we don't want to not necessarily the transactions but the reconcile so what i'm going to do is i'm just going to line those up here and then i'm going to space them vertically so we're going to distribute them vertically and now what i want to do is i want to create some navigation buttons so insert shapes and i'm going to insert this triangle here and i'm just going to put it right about here okay i'm going to make it a little bit bigger because we want to be able to see it and we'll do the height on point 4. so i like that but i do want to give it the same look and feel as we have so again give it that same button here and now what i want to do is now the way i've got it the way i like it i'm gonna duplicate that and i'm gonna reverse it so i want this one here to rotate it and this one horizontally so this is gonna be our previous okay so we've got that there i like that there we'll bring them up a little bit bigger point for cool pic so i'm good with that now all just want to do is i want to make sure that we're right justifying those here and i'm going to left justify this one here to make sure that everything's lined up like that and then we'll just bring these as well all the way on the left everything's going to be lined up on the left not this one this one everything's going to be lined up on the left that looks good everything's good lined up and now what i want to do is we'll just make these a little bit bigger so we have room for the icons and we're going to add icons generally you want to add your buttons first then your icons to make sure that the icons end up going on top right so the things you add after go on top so i'm going to hold down the control line this up and group that that's going to be our navigation we have a save new and delete perfect so let's go ahead and add some icons insert i'm going to insert some pictures here and i've got some icons saved up so basically i'm going to hold down the control and insert those i'm going to change the height on that to 0.25 that's going to give us a little bit smaller that's what we need if we take a look at them i've got one that's going to be used for the icon this is our icon that we're going to put in right here so we want that there i'm going to merge and center this here and then we have space for the icon so i'm going to bring our icon here and bring it down here like this and i'm going to give it a color that's closer to the theme which is about like let's say this one here okay yeah that's closer to the theme so we've got our our icon and i'm going to color this font same the same here like that okay so now let's go ahead and put in our save we got this for our new we got our save for our checkbox and we have our delete okay i'm going to hold use my selection i'm going to line up those icons to make sure they're all lined up horizontally like this and now we're ready to group and save so i'm going to undo that so now what i want to do is i want to group these so i'm going to hold down them lining them up grouping them up and again lining them up grouping them here and we're good to go and last one is for delete so everything's looking good and now all i want to do is use my selection tool and make sure that we're not sizing these and measures so i'm going to right click format the properties go into the properties here and click move but don't size we don't want that changing after we're okay i'm going to save our work use our unselection tool okay good so we've got things looking really good we've got our navigation we've got our conditional formatting we've got everything just the way i like it and now we're ready to add some vba into this but before i do that i want to make sure that our formulas for our names are important right this particular name right this is going to be either interest earned or cashback or credit it's going to be based on whatever our transaction type is our account type i should say is so it's going to be based on that so equals if this here our account type equals bank then we want to show something else so what do i want to show if that's the case i want to show interest earned interest earned otherwise i want to show cash back or credit so it would be a credit card so in that case cash back in this case or credits credit's something you get some credits on a credit card or cash back you might want to list them here so it's gonna be interest earned if it's a bank or cash back or credits you know the kind of thing that brings down your balance inside a credit card or brings up your balance if it's a credit card okay so that's what i want to show okay and what i want to do is i also want to do something similar to this but basically it's going to be the opposite so this would be bank fees right if it's a credit card or perhaps fees or interest paid right so let's put that inside here so equals if account type equals bank in that case what do i want to show in fact i want to show a service chart something that's going to deduct from your account service charge service banks have service charges service charge and so that would deduct from your balance right or if it's a credit card what would increase that balance well that would be interest paid fees or perhaps interest paid that's going to increase that credit card that liability interest interest paid okay so don't hold me if i spell these things wrong okay all right so we've got a service charge so now as we select a different type of account here is what's going on right if it's a visa it's cash back or credit and fees are interest paid if it is a banking account it's gonna be interest earned or service charge these are going to change accordingly as well because they're connected to that okay good i like that first i just want to get to that we're pretty much done with the screen implementation there so that's it it's relatively simple on the screen so now all we need to do is add some vba and the first thing what i want to do is i want to load all of the transactions that have not been cleared not been cleared meaning this checkbox is under for that specific account up until this date if this is selected so basically what we want to do is we have some data here we need to run an advanced filter based on a few conditions of course it's going to be based on this account name so we need to run that advanced filter and we need to have criteria to do that so the first thing what we want to do is for criteria is we want to know if it is not cleared right cleared mean p how do we know that this if we insert symbol right here with that not clear what is that insert and then close that well it looks like a check box right but what if it's that's for the wingdings font but what if it's just a regular font if it's just a regular font we see that it is a disc calibre here this is this but what about if it is not glade if it's checked i want to know also if it's checked so in this case let's go ahead and insert a symbol so we can see what that would be like this symbol here for this check box here and click insert okay and then close that so now it's been inserted again when we change this here and we change this back to a regular font any font we see it's that p right if we use ariel it's a little more clear equilibrium here equilibrium we see it's that looking like p or whatever it is called here so what i want to do is when it's cleared it basically is going to be not this so not so i'm going to use a formula it does not equal this check box here i want to know any transaction that is not cleared this is cleared here this check box and then if we scroll down here we see one that's not declared this is not clear so i'm looking for any transaction that is not cleared with this account and possibly based on this date so let's take a look what is this date this date if reconcile b9 equals true what does that mean all right let's take a look remember we have this brand new thing we remember this is based on b9 so when we uncheck it be nice can be false when we check it b9 is going to be true so if this is unchecked we're not going to be hiding any transactions this date will not plan to we want to show all the transactions we don't want to hide the transactions after this but if this is checked that means we only want to show transactions on or before this statement date so we need to create a filter based on that only if this is true so we can do that with a formula this is the formula if in reconcile b9 equals true then i want it less than or equal whatever's in j3 j3 over reconcile of course is our statement date otherwise just put does not equal empty that's it that's all we need to do therefore if we unselect this checkbox when we go into the transaction this simply will change to does not equal when we'll need this particular transaction when i search for it i want to know all of the transactions that have been reconsidered for this rig itself then we're going to use that as a criteria but not right now right so our first one we're simply loading new ones i want to load a new one so we're not going to use this but when we load the existing one we will need this criteria what we want is we want those results to come from here we want all those results and then what i'm going to do is i'm going to take these results i'm going to bring them directly in here okay all right so that's just what we're going to do so let's write a macro that can do that let's load it based on this account based on this date here okay and we're going to do that right now inside and of course based on the cleared status right if it's cleared already we don't need to load that in so let's go ahead and write a macro now so we're going to go into the vba inside the developers and visual basic alt f11 will get you there and i've got some just some empty macros here we've got a single module here called reconcile macros so what i want to do is the first thing what i want to do is i want to clear some data so that when we have a new reconcile it's going to clear all the data so we can do that here so i've also dimensioned some variables already we have the reconcile id as log the reconcile database row that is the database row that we're going to be focused on right here inside you know 3 4 5 i want to know what row that is on so we're going to have to put that into a variable i want to know the reconcile row if it is on a specific row we need to know that that is the row that we're going to be focused on here what row is it on 11 12 or 13. we're going to need to focus there so we need that i also want to know the last reconsolver what's the last row here what's the last row of the value i want to know that and also i want to know the transaction database row that's going to be focused on our transactions what database row what is the last one if we loop through them i want to know the transaction database row for focus on a specific transaction i wonder the last row of the transaction database when we run that advanced filter we need to know the last row once we get the results of that i want to know the last results row right we need to track through those results we need to know the last row of those results and we also need to know as we run a loop through those results starting at 3 going we need to keep track of what row we're on that would be the result row so we want to keep track of the result row here as long great it's relatively simple we want to know the account name as a string and i want to know the statement date as a date that's it for that so that's all we need to do so the first thing what i want to do is just simply clear out all the information on the news so we're going to do with reconcile now reconciles the sheet name the code name name of that to make sure just hit the dot there the period make sure that intellisense pops up we know we've got the right sheet name so the first thing what i want to do is dot range and i want to do k2 k2 is that id i want to make sure that it is whatever is in b5 so basically when we create a brand new reconcile i want to take whatever that next available request id and i want to place it directly inside k2 so that's what we're going to do with this line of code k2 value is simply equal to dot range whatever is in b5 so b5 dot value and that's going to be the next reconcile id all right so next up what i want to do is i also want to just simply clear out some information i want to clear out everything else so how we go into that dot range in this case we're going to clear out so i want to use dot clear contents and then we're going to enter some clear contents so i've got actually i've got them saved here i'm going to copy them because it's going to be a lot so basically it'll save a little bit of time i'm just going to clear a bunch of fields out all these fields we want to clear out right it's a brand new one so we're going to clear everything out so i'm just going to paste them in here and that's going to be f3 h3 j3 all the way including our full table d3 through k99 now keep in mind that i did include k here k is going to keep track of the row that it's on right i want to know that transaction row what row is it on 9 12 13 right if i know that row i've got that row here so i'm going to bring that row also in there and it's going to be hidden of course but we're going to put it directly in k great so we've cleared that out so we understand that and we're going to clear out those contents and also what i want to do is i just want to make it easier on the user and i want to select a specific cell so f3 that's the account name we're just going to select that so dot select and that's pretty much it for that macro so that's going to do it so all we need to do that so this particular macro we're going to assign to that new button so i'm going to copy that and i'm going to go in here and i'm going to scroll over here we can move this up because we're done primarily and i'm just going to right click and assign the macro to this so it's going to be this one or we can paste it in either way so we click new it's going to do just that clear everything out which is exactly the way i wanted and now we have the ability to select an account and i also can put in any balance and i can put in a statement date so if i put in a statement date now what i want to do is once i put these two in i want all the transactions to load for this specific account that have not been cleared up until this date and only if this is selected but if this is not selected it's going to be regardless of this date this date won't have any time so in this case if it's unselected it's going to load all the transactions for this account that are not cleared that's it that's all we're going to do i'm going to do that with the macro and that macro is called load new right we're loading a brand new right console has not been saved yet so we're going to load that in so how do we do that well with that one it's going to be relatively easy focusing on with this sheet again reconcile and we want to do a few things i want to make sure that they have at least entered two things right remember it's very important we need that account need to know what count to load and i possibly need to know what this i want to make sure that they've got at least f3 and j3 value so we can check that here so if dot range f3.value equals md or dot range j3 value j3 dot value equals empty then we need to let the user know to please fill in those fields then let's type in the then message box please make sure to add in an account and we also want to make sure that we have a statement date statement date before loading transactions exit sub nothing we can do until they do that so assuming that they have we want to make sure that everything's clear but we've got that covered so that is it with the reconcile for the most part until we bring it back in we're going to then shift our focus to our transactions database so with transaction database we're going to focus first thing on what i want to do is i want to know the last row if we're going to be running an advanced filter we're going to be using our criteria all the way from alph n but i need to get the last row in this case the last row is 1 0 0 8 but i want to make sure that we're going to bring that in okay so to do that we need to get the last transaction remember we already got that right here the last transaction rule it is this variable that i'm going to use so with the transaction is going to be equal to basically it's going to be basically on the last transaction a this is the last transaction row if for some reason the last transaction row is less than three then exit the sub because there's nothing we can do right we have to make sure that there's actually data in there nothing to do okay but now we're ready to run our advanced filter so how we going to do that so we've got auto hotkey to help me automate that and so the first thing what we want to do is determine what our headers are where's our headers located well they're located directly in a2 and all the way through j and the row but in this case keep this in mind i've got a result here that's blank why do i have a result that's blank that's very important because take a look at this remember we have a merged cell in fact i'm going to be merging all of these here but what we have a merged cell and that's very important we have a merged cell to make sure that we use a blank cell we've got a blank result here too we've got the description here and a blank one so to do that we want to make sure that our results here have a branch and the only way to do that if our results have blank oh that means our original data must also have a blank row but it doesn't so how do we do that well if we say that our original data goes all the way until column k then that includes a blank data so we can do that blank header so we can do that if we run our advanced filter all the way from a through k and of course it's not the last last row it's the last transaction row and then we're going to use the filter where is that filter come if that criteria is going to be starting in l2 and it's going to go all the way to n3 so n3 that's where our advanced filter is coming up okay and then what do we want those results i want those results to come directly inside here from q through y so that's just what we're going to have inside so q 2 through y so that's going to get us certain but what about our results well we did durbin the last row of the results we're going to use column r to do that make sure that we have that so let's do that so the last results row is going to be equal to let's do here last results row is basically equal to r is the transaction date because that's required very important so we're going to use r that's going to give us our last result row last result row we need to make sure that we have results so the next line of code which i automated if the last result rows less than three then exit the sub check last result row so now that we've checked the last result row we know we've got data and results we can then bring that data inside our reconcile statement okay good so now that we have that so let's go ahead and bring it there so we want to make sure that we do that so with now we need to call out that sheet again here reconcile now everything's already lined up so we need to reinstall dot range d and where's our first row where is it going to come it's coming in d right coming in d and then all the way to k this is where we're going to put that result row and put it in directly in k it's going to be hidden but i want that database row right here so from d all the way through k we're starting in row 11. here we're starting in row 3. so we need to compensate for that difference d 11 through k and the last result row plus 8 right 8 plus 3 is equal to 11. so dot value is going to be equal based on our result so dot range our results are starting in q3 through y in the last results row and last result row okay so that's going to bring over transactions all right actually we'll need to bring through x here i only need to know the row current rule i don't need to know the recurring row yet right that's going to be saved that's not necessary only through this so the credit amount debit not in the row just here debit amount credit mountain row so that's pretty much it that's all we need there so now that we have that we're good to go so what i want to do is i want to simply add a specific character here i want to make sure that they're the unselected right this character i want to make sure this becomes unselected d10 and what is that d10 well if we look in here insert and our symbol we're looking at this one right here it's basically character 168. so that's the one we're going to enter but we're going to do it through vba and i want to put directly inside d10 okay so let's escape out of there and we're going to do that so reconcile dot range d10 dot value is equal to character of 168 that's going to be the selection chain so when we click on that it's going to select all or unselect and that's it so end with and then end with okay so saving our work and now when do we want this to run well i basically want this to run when we make changes when we make changes here or here but i want to make sure that when we run that that they're both have very values in them that's what we do so basically when we make a change that's when we want to do it so that's going to come from worksheet change event so inside the direct sheet here based on that worksheet change event so we're going to bring it over here so reconcile this is the one and we're going to base it on a sheet so it's worksheet here it's change event so this is the change event so the first thing what i want to do is on the worksheet change if the user makes changes to more than one cell i want to get out of that so if target dot count large is greater than 1 then exit sub this prevents lots of bugs okay so we're going to make a change so if not intersection what two cells are we focused on we're focused on f three and we're focused on j3 and j3 so if the user makes a change to either one of those and we also want to make sure that they both contain values and i also want to make sure a few things i want to make sure that they contain values and i want to make sure that we're actually not loading i want to make sure that b2 is false when i load an existing transaction if i search here and i load it we're going to be making changes here right so that that type of change this is going to go to true different kind of change when the user makes a change here because they want to load information here those two different those are different kind of chains so we want to make sure that b2 is false so we need to check those three things so it's and range we want to make sure that f3 f3 dot value does not equal empty and we want to do the same thing for j3 so i'm going to simply copy this and then change the range to j3 and j3 does not equal mp we want to make sure that both of those are and we also want to make sure and range we want to make sure that b2 is false right if it's true don't do anything equals false then and only then do we want to actually load it so once all those conditions then we're going to reconcile i'm going to paste that in so then we're going to run that macro reconcile and then underscore load that is the macro that we're going to run them when those things change so saving our work so far that should be sufficient so when i change this to checking account and let's just change it reconcile reconcile load let's just check on this rig load new okay oops forgot the new there all right so that's sufficient enough pasting that in and then running this there okay and we'll last result row spelled that wrong okay last result row and making sure that we got that variable correct continuing on here and that looks good okay that's exactly what i want let's make sure we format these with currency so we'll just go down here and make sure that those are currency formatted which is what what i want and take a look at here the description these should be merge and center but when we merge and center these is kind of going to mess up our conditional formatting i'm not going to do all of them basically or what i'm going to do is merge and send to them go them to the left and keep that in mind it does mess up so when now i'm going to just basically copy this form actually let's left justify it first right because we should make sure that it looks nice okay so now what i'm going to copy this i'm going to paste the format all the way down here and keep that in mind that it will possibly mess up your conditional format just a little bit and then you might have gradients so what did what i mean by that if i go back into the conditional formatting it's very easy to fix manage rules let's take a look at that notice look at the range got kind of messed up so now it's kind of like convoluted still looks okay but it's still kind of messed up so all we need to do here is to make sure that our ranges are correct so we're starting on e11 so clear that out and we're simply going all the way in this case two all the way through i in this case so we just want to make sure that we have that in this case i so all let's clear that out e 11 all the way to i taking a look at that and then clearing all that out e and then just change it until notice it got changed that does that that does happen when we do that so theoretically what you want to do is make sure that you paste that in okay apply that okay that looks good now let's do the other one and then all we need to do is just copy and paste these and we'll update that got the cleaned up with just those three rules okay so i like that everything's looking really good now what we can do is we can change the font on here make this a little bit smaller these are the row numbers that are associated remember 8 13 8 14 and 15 these are the rows that are associated with our transaction here right so if we come down here we see the row 8 13 so we bring everything in based on that information okay we have just a single we want to make sure that we have just the single type of this only for checking account if we were to change that to a different one we'll make sure that notice that different loads a different type of transaction based on the chase visa credit card good everything's looking good again let's go ahead and reduce this and color these i'm going to take this all the way down here and just give it a little bit of a lighter color and probably reduce the size so i'm going to go into home let's do a 10 and i because we're going to eventually just hide these right here so let's go ahead and and just make them a little bit lighter okay i like that because that we can reduce the column eventually what you want to do is when you don't want to see this you just change it the font color to the same as the background because those really should be hidden for our purposes today i'll make sure that they're visible very good i like that so let's continue on now so what we want to do now that we've got it loaded now what i want to do is i want to be able to click here and select all or select none so how are we going to do that well that's going to be with the selection change event so if we go into there remember we've got characters so i want to be able to select one or more and then have them all select and change that character to the checked box so that's of course in the selection change event so we're going to go back into our reconcile we're going to focus on the selection change because that's the change that we want to make so again we're going to start out again with the same thing that we did before if we select more than one cell we're going to exit out so now if user makes a change to any selection i want to select that specific cell and i want to change it to the checked or i want to change it to the unchecked based on whatever the current value is so to do that we just need to check so first of all if not intersection we're going to check all the way from d11 through d and then we'll just go through a high number nothing right and also there and i want to make sure that e contains a value right if there's no value here right nothing here there's nothing to do i want to make sure that e contains a value before i do anything okay so we can do that here so and range e and the target dot row dot value does not equal empty then we want to do something what do we want to do well then what i want to do is determine what to add are we adding a check are we adding an unchecked box and based on the current value so if the target dot value is going to be equal to character 168 168 then what i want to do is simply add the 254 then target dot value equals to character to 54. else what else in this case else i want to say target dot value equals character 168. that's it and then all i want to do is select something else so we're going to do range f3 dot select and that allows the user to create multi-select so now when we select something select it again so it goes back to the checked and unchecked very very easily here okay i like that that looks really good that's but now what i want to do is i want to be able to select all using d10 so if the user makes the selection on d10 i want to select all or select none based on whatever the value is in d10 so we can do that with another selection change event so this is going to be based directly on d10 only in this case so d10 so once we make a change to d10 the selection change then what do we want to happen well in that case then i want to dimension i want to know the last row we want to select last row as long okay so what is that last row that last row is going to be equal to in this case we'll use column e we want to use that date so e is going to determine the last row e there we want to know the last row of the value right because i want to select them all right and we're using e just in case there's nothing in d so that's going to give us the last row so once we have the last row if the last row is less than 3 then excuse me not less than 11 right checking there less than 11. that's okay for now go back in here less than 11 then exit the sub in case there's no data so less than 11 then exits up okay assuming that we have data then we continue on in that case what i want to do is i want to determine are we going to be selecting them are we going to be unselecting them and of course it's going to be based on the current value so if the target dot value is equal to once character 168 168 then i want to basically or else right we're going to do something else we're going to select them or unselect them so else do something else so in this case what i want to do is just basically change the opposite so this is range including the current one d10 through d and the last row dot value is going to equal character again 254 254 okay else we're just going to basically change it to the opposite so this one simply is the selected one so we can do selected and then otherwise it's the unselected so here gonna copy this i'm gonna paste that directly in here and all we need to do is just change this here to 168. one six eight okay this is going to be the unselected right or unselected or unchecked would go unchecked and then that's good okay so now that we have it we'll call this checked and not selected check so checked and unchecked that's a little more clarity so we've got that saving our work always before running vba now we're going to check it here uncheck it okay the only other thing is notice we want to again select f3 so let's do that so we can click so lastly regardless so range going down here we're still within the original if range f3 dot select so what that does is it selects something else it allows you to click a single cell multiply very easily like click click click click okay so now we have the select select all unselect very easily good so now what we want to do is do a little bit of math to make sure that we have everything correctly so what i want to do is i want to determine what is the clear to balance and it's going to be based on what is selected here so how do we know that well the important thing to know is if it is a bank it's going to have generally a debit balance i mean you can have a debt if it is a credit card or credit it's a liability account it's gonna have a credit balance so basically if it is a bank it's going to be all the selected debits minus all of the selected credits whereas if it is a credit card type it is going to be all the selected credit amounts minus the debit amounts which is generally payments right so credits or charges and debits are payments whereas in a bank account debits or deposits and credits or payments or checks or withdrawals so that we understand that what we want to do is we want to get that credit balance and we're going to basically use the sum if we're going to wrap that around an equals if air and the first thing i want to do is determine i want to add in whatever is in the beginning balance so the first thing we want to do is just whatever's in m3 the beginning balance we're going to start out with it so it's going to be m3 plus and then we're going to determine if i want to know if it's a bank account so if in this case b3 is equal to bank bank then what do we want to do well then we're going to use the sum if based on all the debits minus the credit sum if in this case what are we going to sum i'm going to sum uh one based on the range so what is that range we're going to use d11 we'll use d11 all the way through let's say d9999 let's use four nines that'll cover it okay so but what is going to be based it's going to be based on character this time character chr and character 254 i only want to know based on those selected i'm only selected and what is the sum range well we're going to use the sum range we'll use debit amounts but we didn't create that yet or i did actually debit amounts debit amounts i forgot to go over that one with you it's just a named range it's a dynamic name range based on all the debits i'll show you that in just a moment based on this column here in column i so debit amounts so it's going to be all the debit amounts right here's the sum if minus what minus all the credit amount so all we need to do copy and paste this here paste it here instead of debit amounts we're going to go with credit amounts credit amount so so credit amounts again i'll show you it's a dynamic named range to basically all these columns and call them j so that's all it is okay great so that is it if it is a bank right so that's all we have to do plus what do we do we also i want to add one more thing plus i want to know if it's a bank all of the cash back or all the credits if it's a bank i want to know whatever's in m4 minus m5 right so m4 minus m5 okay so basically we want to know remember the bank it's going to be all the interest that they paid you minus all any fees that you paid from the bank so we want to add in any interest that the bank paid you we want to subtract out any fees that you paid so that's it for the bank right so we're good with the bank so what if it's a credit here if it's false then it's a credit so again we're going to do something very very similar but in this case all we're going to be doing is taking all of the credits here so what we can do i can just copy all of this actually let's just copy the first part of it the credits minus the debit so in this case some if copy that sub credits minus the debit so the total debits just the opposite here so we're going to copy all the debits okay and then also what we want to do in this case is we want to add in add in i want to know all of the fees or interest paid right so how much what do we pay that's going to increase our level plus m5 minus whatever let's say cash back minus m4 whatever cash back they've given us okay so that's all we have there then what if there's an error if there's now i just want to show blank let me just fix this d11 that's not going to work okay we've got to fix that range there should be g11 through d99 made the mistake didn't see it okay good just fixing that up there then we're good to go on our ranges we're ready to go okay i like that there everything look looking really good just into that and we have our balance here so what do we want to know i want to know the difference what is the difference between the ending balance if i've got a ending balance of let's say thirteen thousand dollars right then i need to know the difference so what is the difference there it's going to be simply equals m eight minus three balance right so that's it that's our difference right okay very good okay so i like that everything's adding up to and now let's go ahead and put in some information we want to be able to save this now i want to be able to save this just as we did before so let's assume we have a cashback or credit right on this particular account and maybe we have a we'll call it cash back right sometimes we get points or cashback of 20 and let's let's just say as of 8 20 and also we paid some interest so interest paid and maybe we paid about 60 dollars of interest and we have a date of 8 30 on this 8 30. okay so we've paid that so we want to make sure that the ads in now assuming that we have a correct ending balance let's say we do have a correct ending balance we want to save it our ending our clear balance might be 12 7 5 7 19 okay so that would be clear so what i would like to do is i'd like to have some conditional formatting here and i would like to know if it's off so if it's different if this is not 0 i want to have some conditional formatting so we can do that conditional formatting i'm going to manage the rules i'm going to create a new rule and this can be based on a formula so we can use the formula that's going to be simply this is equal to 0. so when it's zero what i want to do is i want to format it in green so i'm going to give it a font bold which is already already is i guess and then give it this green so we know that it's here and click ok and then apply okay but what if it's not what if its value is different i want to create a new rule again a formula and then in this case m is not a well we can just use one rule right but in this case but it's fine two rules is fine one rule meaning you just color it green and then you color it red when it's not at a balance so that would be just fine does not equal zero and then we give it a format in this case of uh also and then we want to do red so when it's not we don't want it red and click ok and click ok so now when we unselect one we see that it automatically goes but as soon as we select everything it goes to green we know that we've got it in balance okay i really like that what i want to be able to do is i want to be able to obviously click this button here and click save and i want to save this uh specific arrow so how do we do that with that switch with the macro so let's go ahead and go with that macro now and we'll write up that macro so that's here inside our reconcile and that's the next one we're going to write called reconcile save and again we're going to start out focusing on the reconcile so with reconcile and we want to make sure that we take care of all of the required fields so we have some fields that are required so we want to make sure that we do so let's take a look in here remember we want to make sure that we have a count an ending balance and a statement date so to make sure that i want to make sure that b6 is three if b6 is three then i know that we've got them all required so if dot range b6 does not equal three then they're not off it doesn't equal then we need to let the user know so we do that with the message box and we say please make sure to include let them know account ending balance and statement date statement those are all very important statement date before saving and then exit sub nothing we can do unless we actually have that so we've got that we want to make sure that also what i want to do is i want to check for an outstanding balance if to make sure m8 does not equal zero i want to at least let the user know and see ask them if they want to go ahead right it's not in balance they can still save it but at least give them a warning so what we're going to do they're going to check for outstanding balance and put a little note on the above one two we're going to check for required fields check for required fields so as far as the outstanding balance we just want to know in that case if dot range m8 dot value does not equal zero then do what then let the user know so if we want it's an option for the user so if message box was that message box it will just say your reconcile contains an outstanding balance make sure they want to save it outstanding balance are you sure you want to say or are you sure you want to save and we'll give that a yes or no so we'll end that quote and then it's going to be vbs or vb no and then i'll give it a title we'll call it um let's see outstanding balance that's the title and then i want to know equals if it equals vb no they don't want to continue then exit some okay so we're exiting the sub if they don't want to continue all right okay good so that's it's just a single line of code that we covers out so we want to make sure that they're doing that so now that we have that we want to make sure that check what i want to make sure is i want to see what happens if they only put in one or two fields i want to check if this is uh one or two if this is one or two meaning only one field out of the three fields or if two fields out of the three fields are filled out let the user know that they must fill out all of the fields if they're if none of them it's fine they don't need to fill out if it's zero this is zero it's fine i only want to know if it's one or two so that's what we're gonna do a check in this one so i'm either simply gonna be looking for if b7 equals one or two so we're gonna check check for required interest earned or cash back if b7 we're looking to focus b7 that value equals one or right what if it equals two in that case what i want to make sure is to let the user know or equals two then let's give the user no message box let's put this when give it a little bit more dynamic when adding a and we don't know what they're adding right and whatever is located in e5 right e5 dot range e5 what's in e5 why do we want to put that because i want to put whatever remember this is dynamic text right it's either going to be interest or or cash back so why not put that in there in our message box so that they know exactly what it is so e5 dot value one and when adding it whatever interest the dynamic rate and right mode let's do comma and then space please make sure to fill in all fields and then exit sub let me do the same exact thing for the other one except i'm just going to change the references so i'm going to copy this here i'm going to place that directly down here but this time we're going to change this check for the required let's say interest or interest let's call this uh let's call this uh service charge or interest paid service charge or interest paid we'll call that okay we'll escape out of there service charge and debate court service charge or interest paid and this of course is going to be based on b8 b8 is what is what we're using here b8 this is the three fields that are focused on there there so located in row seven for that so b8 so make another change if b8 equals one or b8 equals two then e7 when adding a whatever's in e7 please make sure to fill in all the fields exit sub okay we're good to go i like that checking to make sure that the required fields and got the creditors now what i want to do is i want to check for transaction items to make sure that we have those so check for minimum items okay the first thing what i want to do is i want to set the last row so in this case we're going to set the last recurring row is going to be equal to and we'll base this on e so column e is where our data so that's going to be our last reconciled row it should be rec where we can solve rho and that that's going to be the last one okay so what i want to do is i want to check if the last recall row is less than 11 then let the user know then let the user know they need to have a message box in this case we'll just simply say please make sure make sure your reconcile statement contains at least one transaction and then exit the sub okay assuming that it does then we can then move on okay so now we have it so now what i want to do is i want to basically turn off calculations right because it's going to be a lot faster if we do that so application dot calculation equals i'm going to turn it to manual and then before the macro ends we need to turn it back on to automatic so i'm going to paste that down here and then make sure we turn that back to automatic before our macro ends okay so inside our macro then we can do everything else because it's going to make it a lot faster because we do need to loop through a lot of rows okay i want to set the account name the account name is going to be equal to whatever's in f3 so equal dot range f3 is our account name we'll call that account name i also want to know the last let's just say this the statement date actually statement date is going to be equal to also j3 that is our statement date we'll put those into variables j3 and that's our statement date okay once we have that i want to check to see if it is a new reconcile an existing b4 is going to tell us just that right here inside that b4 if b4 is blank we know that it is a brand new one otherwise it is an existing so let's write that in right now if start range b4 dot value equals empty then it's a new one then it's a new else existing existing okay we'll do two things if it's existing and a few things if it is new so we'll put that it's called a new called new reconcile okay so with the new reconcile what do we want to do well i want to know the database row database row reconcile database row what row are we going to be placing on it's going to be that first available row inside our reconstant database in this case it's six so what i want to do is get the last row and then simply add one based on that reconsol database so it's going to be the recon database dot range and you'll use a and then 999 dot end excel up dot row plus one first available row so once we have that i also want to know the reconcile id what is that next id well the next id is located directly inside b5 we can pull it from here it's also pretty much here in k2 but i want to pull it the next one based on that max formula we added located in b5 okay so we can do the result equal to dot range b5 it's going to get us that so that's give you the reconcile id okay so once i have that what i want to do is i want to make sure that we place that id directly inside the first column that's only going to be for new i'm going to place it directly inside here a and whatever the row is so reconcile database.range a and the recon database row dot value is going to be equal what recon id set reconcile id okay so once we have that we set the id that's all we need to do for new so what about for existing for existing i just need two things i need to know what row what is that row that low row is located whatever's in b4 b4 is going to track that so all i need to do is copy and paste that in b4 okay so that's our reconcile database row and i also want to know what the id is that's going to be located in k2 for the existing so i'm going to copy this it's going to be equal k2 is where the existing error so they'll call this reconcile id that's all i need to know for the existing everything else is going to come regardless if it is the new reconcile or existing so what i'm going to do is i'm going to copy this and we need to fill in some other information so what do we need i need b c d and e for count beginning balance ending balance and statement d i want to add all that in so we can do that here so b i also need to know c d and e so we just need to simply change these and then paste them in d and then e here so what are those things so let's put a comment before we add in everything else so that we know what they are bring this down so we can keep track of each one we know that this is the account name account name we know that the next one is going to be our beginning balance beginning balance and we know that the last one is going to be our ending balance which is in column d so call this ending balance and then lastly we have our statement date so statement date so now we can do the rest so it's going to be equals we have this one in a variable so we're going to put the statement date we've already set the variable above right here we know the statement date so we also want to know the beginning balance and the account name we also have into a variable so let's put that in there so account name we put that into a variable right there and then if you're not sure if the variable is right just lowercase it and change it up next up all we simply need to do is just copy and put the range that those are located in here so it's going to be oh i might as well copy including equals a little bit quicker for typing and then we'll just update the ranges accordingly so all we need is the beginning balance and the ending balance so the beginning balance is located directly inside m3 and the ending balance is located in h3 so we can just update that the beginning balance m3 here and the ending balance is in h3 good so we've got that we're all set up with that ending balance we're good to go so now what i want to do is i want to we've saved everything for the database but now what we want to do is we want to save the information changed i want to know change if it's cleared and i want to know what row it's on and i want to save that into my transactions right we've got our original transactions here but i want to know a few things i want to know whether it's cleared i want to save that and i want to know what row it's been placed here and i want to know the id associated with that too so i really want to place those three things into each individual row so we can do that with just a few lines of code and loop so we're going to do that now but before we do that right before we add in those what we need to do is i want to check we need to add these items if the user has added in cashback or credit or if you i need to know if they we need to add those we need to add those as individual transaction items at the bottom right but i also want to know have they been saved yet if they have already been added i'm going to put a row a database row here or database row so i need to check here if one are there do is this showing a three or is this showing a three if this is a three then i know that we need to save this do i save it to the existing row maybe we've added it before if this is blank it's not it's been saved yet so we're going to run a few checks i want to save these as individual transactions so how do we do that well we can check to right here with a few if mine so here we go let's check add we'll call this interest earned for cashback and then if any so if dot range b7 dot value equals three then we're going to be adding it in and then end if so if it's equal to that means we do have to add in but now i need to know have we added it before in other words when we load a transaction we're gonna and there already was a cashback associated this will have a database row in other words it's gonna have a row probably all the way it's gonna look something like this right notice that this is the row five and this is the row seven this is row five and this is row seven so in previous transactions in our data we've added it in already so i need to know that right very important so we have to know if it contains a value so when i load that in so we need to check has it been previously saved to the database or not how do we know that well we know based on whatever is in k5 if dot range k5 dot value equals empty then we'll just call this new new transaction else existing transaction so again if it is new all we need to do is determine what row it's on it's going to be the first available so the transaction database row is going to be equal to transaction database dot range and what is that range again we're going to use a 99999 dot end xlr dot row plus one first available transaction row okay so also what i want to know is there's a few things that we're going to add only for new what are we adding in well the first thing i want to add is the the id the recurring id here and i want to add in the row here so that's gonna so we're gonna yeah h i and j those are all for new transactions right so gonna add those three items right here let's do that right now and also probably we're going to add in the cleared status because we know it's going to be cleared automatically in g because anything that they put in is automatically cleared so let's add that in right now so here we go so we're going to start out with whatever is located in h so transaction database dot range h we've already assigned the row and the transaction database row dot value is going to be equal to what do i want to put inside h let's bring this inside h i want to put whatever row what row is this on well this is row five right we're no we're no in row five so i'm just going to put in a five directly equals five set row okay all right so that's going to go in h and then beyond that what i want to want to i'm putting the id goes in into h and the row goes into i okay we got that covered all right so let's just copy that and that's fine and then we're going to paste that in here h and inside i of course that is going to take on our row here so that's going to put that and our id which we've already defined inside a variable equals recurring id call this set id set id so the id goes first the row goes second and then what else do we need to do well certainly we need to add in whatever's located i want to put that row and that's just a formula right i want to know the row so we're going to add in that automatically copy that and here it's going to simply be a formula so what does that formula equals in parentheses equals and then row so that's all we need to do just add in that formula right there so once i've got that that's going to set our row called a set row formula so that it's always dynamic that's why if we delete any rows it's always going to be there we don't need anything and then also we can put in our clear status but our clearance is going to go in regardless if it is new or if it isn't existing so that's fine so cleared is going to be regardless so this is only if it's new so if it's in existing i want to set the row what is that row is going to be located in k5 transaction equals whatever is in k5 if it's an existing equals k5 okay so now all we need to do is add in all the other information for this so what type of information i want to add in the date the account uh the description here and the amount so let's do that right now so while we're focused on the transaction database so it's going to be here transaction database dot range a we're going to put that first thing a what's going in a well that's going to be the transaction date and what is the row number of course we've already set up a transaction database row dot value where is that date coming from that date is going to come directly from whatever is located in j5 so j5 is we're going to put that so let's place that date right there okay inside the code equals dot range j5 equals let's call this the transaction date that's not the reconciliation date that's the transaction date we have to fill in some other fields so we can do that right now of course we need to put in whatever's in b here c d let's go ahead and go back into the database here here sure we need the account we need the description uh d we're not going to put in a reference and then e or f in that case okay so b of course we're going to take on in this case the account needs to know what is the account so how do we do that that is simply the account name equals we've already in that variable account name that is the account name pretty self-explanatory there so what else inside c column c is going to take on that description that description is located directly inside f5 equals to range f5 so we put that description there so now that we have a description and also what about in the next column well basically what we need to do is i need to determine if it is so basically we want to add in the debit amount right so the debit is going to be in column e right there's no reference or nothing in d so e is going to take on that debit amount and where's that debit amount located it is located in h5 equals not range and we could just copy this here and just change the range here and then make this f5 so that's the debit amount okay we'll call this debit amount so now that we have the debit amount column e we also want to make sure it's cleared right clear is located in g so i'm just going to copy this and then update it to g so this is going to be g and what is our debit our cleared icon of course that's going to be simply our character 254 character 254. that is our cleared icon if you want to call it an icon so that's it that's the just call it check box we'll call this checked box good to go so that's all we need to do so now all we need to do is pretty much just copy this and do pretty much the same thing but change the range for the next one right so all i'm going to do is do this add in this case i'm going to copy this here everything we've done i'm going to paste it down here but now this case we're going to not focus i'm going to focus on b8 and this time it's not interest earned what are we focused on here they're focused on this one right here the fees are interest paid so it's going to be the service charge or fees or interest paid so add service charge or fees or interest paid interest or interest paid and of course we're focused on b8 right it is now b8 that's gonna take if b8 equals three then we know that there's actually needs to be end so we just need to update the ranges in this case in this case it's not k5 it is k7 and the transaction database is going to be the transaction h is going to take on right this the set row we're going to take that on a 7 right transaction database row in this case is going to be 7. in this case it's going to be j7 in this case it's going to be f7 much easier once we keep some uniformity and this is going to be h7 so that's it that's all we had to do for those okay so we've covered both the service charges interest paid and we've covered the cashback in case there's any so now what we need to do is just simply add in the items and so we can write some text right here so let's call this update cleared or uncleared items with cleared or uncleared items so we can set a loop right we know it's going to start at 11 for reconcile reconciliation row okay so then of course we need to close our loop make sure we got that correctly and then we're just going to call it next freaking row so we've got that there so now all we need to do is simply loop through those and then add items accordingly okay so let's take a look at that if first of all what i want to know is has it been previously saved right k is going to keep us out i want to make sure that we have a row there so just k if if it doesn't contain a row then we can go to the next item right i want to make sure that it contains that row so if dot range k and reconciliation row dot value equals empty then go to next item okay so we're going to go down here and i'm just going to write a next item with a colon okay so that way it's going to skip everything else if there's no database row then there's nothing we can do so now what i want to do is assign that database or a transaction database row is going to be equal to whatever's in the k and the reconsolation row so this is the transaction database row very good okay so now that we have that what i want to do is just make the cleared status update so how do we do that transaction database in this case dot range h that's located in h right this is what we want to update all i want to do is update what's located right in h and i want to do the id right and i want to do the clear so the cleared status is in g while the reconcile id is an h and i also want to make sure that we're updating i and make sure that j contains a value as well so they just double check to make sure everything contains so the first thing what we want to do is update let's go with g so g is going to be on the first one it's going to be that status that cleared or unclear so g and transaction database row dot value is going to be equal to whatever is located in d dot range d and reconsolation row right there so that is the cleared or uncleared status cleared or uncleared and next up all we need to do is just copy a little bit of this and then we're going to just make the updates accordingly so g and then we have h is next and then i is next after that so what will go in h well in h i'm going to make sure that we add the reconcell id we already have that inside a variable here so we can just put in the ric id reconcile id and inside i we are going to put in the reconstitution row i is going to take on the reconstructed row and lastly what i want to do is i'll put in j j is going to take on that formula that we put in there simply that formula that's going to say the row just to make sure that we have that equals in this case quotation equals and then row very good i like that that's going to take on the formula next item in case there's no item going to go down that's going to loop through all you know we're going to restart re calculations we want that i would like a message box too letting the user know that it's been saved so after this what we can do is we can do message box and we can just do something like reconciliation save alerting the user know that it's been saved and then what i want to do is i want to run a macro basically that clears it out and undo so reconcile new right we have that macro ready that was the first one that we did so that's going to clear all the items right up here i just want to run this macro that clears everything else out i like that now let's go ahead and save our work we'll go ahead and assign this particular macro to the button that we created on the save which is here and then click assign the macro and then this is good okay good i like that now all we need to do is just check for any issues that we might have fix those after they come up and then we will go ahead all right let's go ahead reconciliation saved all right taking a look at the data inside here let's take a look at data visa okay we didn't have beginning balance this looks right uh transactions uh this one we need to get these we want to make sure that the row goes over to j and not i okay let's just make that update that should be j right we want to make sure that each one contains j double check on that and so we'll go up here right we want to make sure that it is j right let's fix that j for both of them right and then all the way up here j right we want to make sure that those are j so this right would be five and seven this would be here and then this would be here right we just fixed that okay good so that's what we like all right so we've got the rows in i right 5 7 and in j we have the actual row of that so and together j is going to take on that formula here and j is going to take on the formula right here and then i is going to take on the 5 or the 7. so we fixed that little issue everything else looks good it's been cleared out now what i want to do is notice they've all been assigned that particular item so we have that in there now all we need to do is just load in so look for it's contained that for like you've got that rig style id here so what i want to do now is i want to be able to put in the four here and then i want to have to be able to load in what we just saved okay so let's write a macro to do that now that would be the reconcile load so that's the next one we're going to do so it's called the load save it's been previously saved we're gonna now load it in so let's get that a good start all right so we're gonna focus again on with reconcile and we wanna make sure so if b4 equals empty right if i put the four in here we know it's going to contain a row whatever the user has searched for inside the change event when they make a change here if they've put in i need to add that if they put in an id here put it directly inside here inside k2 what that's going to do is generate a row here if that row is blank in other words if they enter something incorrect b4 is going to be blank so all we need to do is check to see if b4 contains a row so that's the first thing we want to do if dot range b4 dot value equals empty then let the user know message box please enter a correct reconciliation id exit sub nothing we can do there unless they enter the correct one assuming that they have once they have we can assign that a row so the first thing what i want to do is i want to set that load to we're going to be loading in all the information here so b2 is going to go to true once we finish it'll go to false.range b2 got value equals true set load to true and then of course what we want to do is make sure it goes to false at a certain point so i'm going to copy this down here and then i'm going to paste it down here and then we're going to go to false and change this to false let's call this set load to false and then just change this value to false okay and everything else is going to go in between there so once we have that we can then continue on what i want to do now is i want to make sure we get that database row what's located in b4 and that's going to be simply be called the reoccurring database recurring database row is going to equal whatever's in b4 that's our database or database row okay once we have that row we then can move on so we want to then clear out anything that might want to clear out anything that might be if there's an existing one we need to clear all that out so we can clear existing i'm just going to copy and paste it as we did before but so i'm going to paste it right down here so basically this entire range we're just going to clear the existing fields okay we're going to do that all right so once we have that i then want to load in all the information right inside f3 i want to put in the account inside h3 the ending balance the statement date the beginning balance i want to put all that inside here so dot range f 3 that's going to take on of course our account and where's that going to come from it's going to come from recon database dot range it's going to come from column b there and we've already defined the recon database row there dot value so that of course is our count name and then we just need to make some updates according to the other so in f3 of course we didn't use data mapping in this case but that's okay it's just a few values not too many so h3 is going to we have m3 is going to take on our beginning balance so put that beginning balance and that's going to come directly from column c inside there inside column d is going to take on our ending balance and where's our ending balance going to go to any balance is going to go directly inside right here located in h3 so it's going to come into h3 okay so once we have any balance what i also want to have is for coming from column e that is going to be our statement date so we'll put in statement date here and our statement date is going to go inside j3 so that's going to load up all the main stuff so that's pretty much it with that so that's that's all we need to do as far as the top section the main section so we're good to go on that we've loaded it in remember those triggers the reason we set this true back to false those triggers are account name and statement date f3 and j3 so we set b2 to true and set it back to false because of the triggers on f3 and j3 those are the triggers here that we don't want running these triggers right here on the change f3 and j3 those are the triggers we're avoiding so we set it to true because we want to make sure this isn't going to happen this isn't going to run if b if oh it's only going to run to happen when b2 is false if b2 is true this is not going to write we don't want to load a new one we don't want to run that because so that's why we set b2 to true and then back to false so continuing on now we're going to focus on the transaction database we've now loaded the account name the ending balance the statement date and the beginning balance we've loaded those four fields in this should be an if error in case there's a value so if error all right we don't want to see that if there's anything comma and then the space here okay so that's it that's what i want saving our work so far and then we can continue on and now we want to do is want to focus on that transaction database so with transaction database and what are we going to be doing with that well we're going to be running an advanced filter so the first thing we want to do is get the last transaction row so the last in fact we've already written it up here so we just need to write just select it up here because we already determined it up here right here so the last transaction here the first available row so we want the last transaction database row it's going to be right here so that last transaction database row we can put it down here last transaction row it's going to be equal to whatever is in the transaction database we already have the transaction database there hey so that's our last row so once we have the last row and why do we want the last row well i'm going to run that advanced filter i'm going to get that last row we need to run an advanced filter but this time what i want to do is i want to run the criteria a little bit different this is going to be based all on the reconciliation id if we look at this is our criteria this is based on k2 so that means as soon as our macro puts whatever they put and we haven't done that part yet puts it here in k2 whatever's inside k2 that is going to be our criteria so once we know the criteria we can then have our results come back into here so we know that but to do that we need to get the last row if the last transaction row is less than 3 then exit sub okay nothing we can do there ok so now what we want to do is run that advanced filter because we're going to just as i mentioned before and we're going to also do it again just like we did last time right so we're going to run it based on this time our of course our criteria is going to be slightly different but it's going to be based on a2 all the way through ku using that blank column the last so that's what we have to do here a2 through k our criteria is going to be o through 2 through o3 so we're going to change that to o2 through o3 we want to copy it to the range the same as before we're going to use that same one copy it to that range we could have copied and pasted that advanced filter but it's okay q through y having that come through q here q 2 through y and then of course we want the last results row so meaning that we have the last results row and because the last of the row equals basically this is going to be based on that date which is going to look like an r now if the last result is less than three then of course x of the sub assuming that we have data then we can then continue on we know that we can continue okay so now what we want this is the last time we're going to exit out of the step before moving through so to make things faster again we're going to use application calculation application dot calculation equals manual and then of course we're going to change it directly to automatic before we end so we're going to change that here equals calculation automatic okay because inside here we're going to run our loop and we want to make sure things are fast and we can do that when calculation is set to manual so once we have that we're going to run our results for our loop result row we know starts from 3 equals 3 to last result row and then close our loop next result row so inside that's where we want all the action to happen okay so first thing what i want to do is i will need to know right there's a little bit difference if the recurring row which is located in y we're going to set that up i want to know if it is located 5 or 7. that's very important because i want to set it up so let's go ahead and write that in check for right i want to know are we adding is one of the transactions check for service charge or interest transaction what do we mean by that what i want to know is is one of these transactions that we entered is it possible that it's one of these or one of these and how would we know that well i would know that based on the row right the reconcile row and that's all the way down here right so we know based on this is it five or is it seven if what is that whatever's in i and there if it's five or seven then we know to place it directly inside here here and here otherwise we can bring it down here so we need to differentiate between those two types of transactions so we can do that with just a little bit of code so first thing what we want to do is define the value there so the reconciliation is equal to y and the result row y column y remember it's coming directly from our results here inside y which let's go all the way up so we have some data to look at so it's going to be here once it comes up okay but we didn't have any because we didn't run which is the row but this time we're going to run it all the way to y our results are coming directly from y so i want it in there so that's what we're going to look at so y and the result row and the result row this is our reconciliation or reconciliation row now what i want to do is i want to check is it five or is it seven if freaking row is equal to five or rican row it's equal to 7 then do something else to something else let's just call it service or interest just to save a little speed there okay so then we know it's one of those so if there then what we need to do is add that information in so reconcile with the sheet reconcile dot range f and the reconcile row dot value what's going to be an f f is going to be coming i want that description that description is going to go directly in f it's going to go either in 5 or 7 based on the row so i want that there so f is equal to it's going to come from the description where is it dot range where's that description located that description is located directly in column s so that's what we're going to do s and the result row all right so we've got that and that is of course our description what else do we know of course we need to add in the amount and we need to add in the date so where's that so but first of all i what i want to know based on that i want to know are we adding in a debit amount are we adding in a credit amount so that's very very important so we can determine that with an if then statement so let's call this add amount credit or debit credit or debit okay so if not range v we're looking at v in the result row row dot value because it's going to be one or the other it does not equal empty then what i want to do is update that reconcile in this case reconcile you need to call out that sheet range in this case h and the recon row dot value is going to equal whatever is located in w it's going to be a credit right if it's v does not equal empty then we're going to put in v of course then we're going to put in if it's not empty we're putting in here the debit amount right there okay that's works else we're going to put in the credit amount else putting in the credit amount this there we go just copy this but instead of course column v we're going to put in column w so i'm like this i'm putting it in column w so it's either going to be i'll notate that w so this is going to be the debit or this is going to be the credit so i want to make sure to put in the one that contains the value okay so here we have v contains our debit amount or our credit amount located there because only one is going to contain a value okay so just like that all right next up what we want to do is in j right j is going to take on our date so all we need to do is just pretty much copy this here and then update that and so it's going to be j right our date j is coming from our data is located directly in column r so that is our transaction date transaction date here let's take a quick look at that so we can see j column j whatever row 5 or 7 is going to take on and it's going to come directly from our column r okay so we got that covered there that's the transaction date that is it that's all with you but we do want to update that database row i want to make sure that row whatever row is going to be placed directly inside k remember that's very very important so k is going to take on that as well so we're going to paste that in inside k we're going to put in directly what is located from our column x x is going to take on that right here and that is our database row database row right because we when we make that update we need to make sure that it's updated and i want to make sure that k also gets clearer k5 and k7 also get cleared out too perfect that's very exactly what i want all of this is for service or interest but what about if it's just another one what about if it's just a standard let's put in the standard transaction in that case we just have a little bit to do in that case all i really want to do is just bring over all the data right so i just take this q all the way over here to the r and then just bring it on over so that's all i need to do through x q through x you're going to bring it all the way from d through all the way through k so d through k is equal to q through x so we can write that in right now in a single line of code so we'll go reconcile.range d starting always column d and the reconcile row and through all the way through k and the reconcile row dot value is equal to dot range starting with q and the result row and the result row and colon all the way through x and the result row dot value so that's going to bring over bring over all the transactions including the bring over all transaction data okay so that's going to bring over that single line of code so that's it so all we need to do that's end if right so that's the only thing for the standard transaction and we're just going to loop through the results okay just like that and then of course application calculation back to automatic saving our work and when do we want to trigger this well i want to trigger this this when the user makes a change to what when the user enters a search right when they make a change to m2 that is exactly when i want to take this whatever they've put in assuming it's not blank and put it directly inside k2 so that's a change event back into the reconcile i'm going to focus on the worksheet change but this reconcile this change is going to be directly based on that cell m2 so we're going to change this to m2 we also want to make sure that m2 is not blank and range m to dot value does not equal empty then do what then do a few things okay so the first thing is what i want to do is i want to take whatever they've entered and i want to place it directly in k2 so range k2 dot value equals range m2 dot value then i want to run the macro that's going to save that all right and that's it that's all we need to do for that very very simple so what i'm going to do is i'm going to save our work always save before and then i'm just going to double click this or enter 4 again and then we'll check okay last row is not defined okay so that should be the last transaction row right from our advanced filter we know that continue on and then let's take a look alrighty let's take a look we'll fix that that's just going to be what i'm going to do is i'm going to round this the reason why this is and let's take a look at this i want to show you something here why is this at 0 why is it showing it's not really zero it's kind of strange uh equals okay so i'll just put that in another formula here equals now it looks like zero but if we bring out we can see that it's actually not zero it's you know we need to round it up so let's bring this down here and then i'm going to increase the decimals a lot and then we'll take a look all right we can just want to round this up make sure there's just two digits so i'm going to do that just round it to the two digits so round making sure that it's not more than that because that's gonna ensure that we hit it is zero so two like that and now there go that goes green okay so basically it's like a very very minut you know sense so that's what we handle that just handle that with a round okay great so let's take a look we've got zero everything is safe if we were to have a beginning balance and of course it wouldn't bounce but it would all we would need to do is just change it to thirteen two five seven point one nine and it would balance okay that's looking really good all right cool so we've gotta everything's looking good i don't necessarily like these buttons here let's move it over and then we'll just bring in the center of this a little bit i like that better and i'm gonna do the same thing with this one new so i click the macro that's working very well and then new and then bring it over a little space okay that looks very nice okay so now what we want to do is we just want to make sure that these navigations we know how to load it up everything looks good let's double check everything we've got our notice we've got our database rows here right we've got this i'd like to what i'd like to do is when we click new and we click an account in here let's just put in uh 915 right and so it's loads what i'd like to do is run this macro based on this so i want to assign a macro to this the one that we've already so it's going to be called the load new click okay so basically what i want to do is refresh this based on this list so that means statement date so that we if we do 9 1 right let's do this a date and we hide it it's going to change those transactions so i don't want to see anything after that notice it should only be showing down what we need to do is we need to clear out all this data i want to make sure it gets cleared out on that so we notice if we take a look at our transactions we see that just one line which is what we want but we didn't we need to clear out the data on load new so that's very important so at least i just want to clear up basically what i want to clear just the data here that's all i really want to clear out on load new so we're going to do it directly with the reconcile so not range we'll already copy that here and then all i need to do is just clear out this contents right here d11 through k99 clearing that out and we're loading and we need to make sure we do that saving our work and now what we can do is now i like that now we get that's exactly the way so we're hiding the transactions only on before that so that's what i want to see perfect so we only want it to go up and down so for example if it ends if we say 9 15 we only want those transactions so again changing this to september 15th we'll also do that so it should show everything including after which we want but when we select this we want to automatically update showing only dates up until 9 15. perfect that's exactly what we want okay so last thing we have to do is just get these navigation and then we're going to be done on the delete right i'm going to add this one for our patreon right so if you want to learn how to do the delete try it on yourself let me know i'm going to be creating this solution directly for our patreon inside that you join us it's starting at just three dollars a month 10 or 20. what i do is i create brand new amazing features or fixes or anything else you might want to see on these existing i'll do a new recording i'll add an updated workbook so i do would love to see you join it's a great way to support these free trainings as well okay so let's go so that'll be for delete i'll hold off on that but i do want to create these navigations i want to be able if i'm on reconcile five and i click previous i'm gonna go to four three two and one and so on and so forth so let's get that done before i let you go and we're just gonna call that the navigation so those are the last two macros that we need to go over today and that is right here previous and next so what are we going to do so as far as the previous so let's go to with reconcile and that's the sheet we're going to be focusing on and then what do we want to do well that basically what i want to do is i want to be able to determine what is the current row or we do have a current row have we selected it right for example here there's not really i want to know are is there a current row selected what do i mean by that rican's row if i know let's say we're on 3 if i know it's 5 then the previous would be two the next would be four but i need to know what row we're on if there's any row maybe there's not so first thing i want to check what's located in b4 is there row so if dot range b4 dot value equals empty then do something and if so what are we going to do well in this case we're going to call this no current reconcile so when they do previous what do i want what do i want to what do i want to happen if there's nothing i want to go to the last one right if there's not four on five i want to go to four basically if there's no current one i just want to go here all right so that's all we're going to do find the last row and go to whatever the last one assuming that there is data and there's data so to do that we could do the last recurring row it's equal to recon database dot range and it's going to be a 99999 dot end excel up dot row okay so that's the last row so let's take a look if the last recon row is less than three that means there's no date at all less than three then we need to let the user know message box there are no previous just put something like there are no previous reconciles previous reconciles okay exit sub nothing we can do assuming that there is then all you need to do is take whatever's in there and put it directly in there so assuming that there is data dot range m2 is all we need to remember m2 if we make a change then it's going to automatically trigger that all i need to do is m2 value in fact k2 all i need to do is trigger whatever's in m2 because that's just like the search any change there is going to put that value in k2 so m2 is going to be equal to what you can probably figure it out m2 is going to simply be the last row and whatever is located in column a or so m2 dot value is equal to recon database dial range a and simply the last recon row dot value because with the last icon id that's all we're doing is placing the last one in there so now that we know the last save one there else what else but what if in this case let's take a look at this else what if b4 is not empty in that case what we want to do is determine the next one else how we going to do that well we can do that simply with the current row so else if dot what if it's three dot range b4 equals three what's that going to mean if it's three and they're trying to go previous there's nothing beyond that so we need to let them know that they're on the first one they can't go anything before that then do something message box you are on the first reconcile exit sub i'll fix that spelling here exit sub okay so nothing you can do if they're on the first one no all right just let the user know assuming that they're not on the first one dot range m2 m2 dot value is equal to recon database dot range a we've always focused on column a and whatever is in b4 we could probably just put this into a variable probably a little bit easier but that's okay a for that value minus one dot value what does that mean basically whatever the value of this in here let's say it's row 5 i want to go to row 4. so 5 minus 1 is 4. so whatever's in a4 we're going to take on that that's the previous id previous and that's it that's that's all we need to do that should be sufficient enough so let's save our work check before we do it and then what we're going to do is we're going to copy this and just add this to that button the previous button and then all we need to do is the next button here so this is the button that we're focused on right we're on the last one right click here again inside there assign the macro paste that in there click ok now put it in here four three two one perfect okay now we need to do is the next one so we can do that but now all i need to do is just simply copy and paste and make a few changes so i'm going to copy this here i'm going to paste it down here but this one's a little bit different right so let's go ahead and put the last recurring i want to know the last row let's put that up first right last recurring row now if b4 is empty which we need to know if there's b4 then what i want to do is i want to know the first one well we know the first one in case there are but basically if there if there's nothing there if there were not on a current one i want to show the first one whatever's located in a3 but i need to make sure that a3 contains a valley if there's no data in this table there's nothing we can do to do that we just need to check the last row if the last is less than three there's no there's no we can just put in this one there's no current one so there are no previous reconciles or no current let's say no current reconcile i think that's better no current right so this one there are no if it's less than three there is put is no reconciled data reconcile not even a first row in other words nato okay exits up okay assuming that there is then we can do it reconcile right we're already on there m2 is going to be equal to a3 right we know it's to be the first one so we can get rid of it right a3 the first first we're going to put this not the first in this case we want the first not the last the first we're gonna else what is this let's fix this up give room add the parentheses there okay else what's this that means there is a current currently selected reconcile okay this case if b4 equals we don't i want to know if they're on the last row the last recurring row the last reconcile row in that case if they're on the last one you're on the last one let them know the last right they can't keep moving forward they're going to go one two three if we're on the last one they can't keep going down there's nothing there right so we need to know if they're on the last one and if they are we need to let the user know assuming that they're not on the last one m2 is going to be equal to a whatever's in b4 in this case plus one and it's going to be the next id not the previous next good so that's it saving our work as always before running the macro copying this here we could also selected from the list that would not be a problem going back in here right clicking here and then assigning this uh sign that macro pasting that in here clicking okay so we're on one we know we're on one we want to go to two next let's end the property let's we only need one of those n subs not two continuing on no k that's going to go to two then three then four and then the last one you're on the last reconciled did i spell that one probably not you're on the last reconcile i spotted right for a change okay that looks pretty good i have shown you how you can reconcile any type of account whether it is a bank account or a credit card account we can add in other transactions additional ones like cash back and credit and interest paid and interest earned and we can also run filters and automatically use conditional formatting both in the table and both things and that's going to be it if you do like these trainings a great way to help us is to pick up my 200 workbook zip file it's just 77 that's less than 40 cents per workbook template and it's a great foundation you can create any type of application you want it also comes with an incredible workbook library it's going to give you a single click to you can open the workbook or single click to go to the training video thank you so much i really appreciate the time you put into learning it will help you incredibly in your future and i'm glad you've chosen to spend the time with me in these trainings thank you so much we'll see you next week for a brand new training [Music]

Channel: Excel For Freelancers

Views: 97,613

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Reconcile Excel, Excel Reconcile, Reconciliation Software, Free Reconciliation Software, Free Reconcile Software, Excel Reconcile App, Reconcile in Excel, How To Reconcile Accounts, How To Reconcile Excel, Excel App Reconciliation, balancing books in Excel, Import Accounting Excel

Id: gDZ33ceNWTQ

Length: 120min 12sec (7212 seconds)

Published: Tue Sep 28 2021