Rupesh asks how to increment an invoice number in the form of YYMMM001, where the 001 should count up until you run the macro in a new month. A bit of VBA solves the problem.
Transcript of the video:
MrExcel podcast are by Easy-XL.
Learn Excel from MrExcel podcast episode 1812.
Increment the Invoice Number in the format of OCT13001.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in through YouTube by Rupesh.
Rupesh was looking at my earlier YouTube video about the next invoice number program where we just had an invoice number like 1001 and so, the Macro was just adding l what if what, if your invoice number is more complex.
For example, here it's October 2013.
So, his invoice number is Oct13 and then 001 and he wants to increment that to 2, 3, 4, 5, 6, but when we run this on November 1st have it automatically changed to Nov13001 and in January, Jan14001 and start counting up again.
Well, yeah that's possible with a little bit of VBA.
So, we're going to press...
Well, first off make sure that your file is saved as .xlsm if it's safe does .xlsx you won't be able to have a Macro.
So, say that and then Alt+F11 start with Insert, Module and we'll just create a little module here I call it next invoice first thing we're gonna save the old invoice number from range G1.value.
Now, I'm gonna go back to Excel make sure that you're wherever your invoice numbers save, this is cell G1.
You'll have to change the Macro for that and then we're going to use the left function just like in Excel to get the five left characters that'll be sort in left date and then to see what current month it is we use a VBA system variable called date and instead of the TEXT function to format it in MMMYY format we used the format function in Excel you to use text here in VBA we use format and then we have to uppercase that.
So, in Excel upper function here in VBA you Ucase.
Hey, this is great I thought this was going to be a short little Macro and I already we have two functions that are different in VBA from Excel.
So, normally we'd use upper and text here we're using Ucase and format if the left date the left 5 characters is equal to the current date, then we have to do something tricky.
We're gonna take the right of the old invoice comma three that's the sequence number add one and then format it in 000 format.
So, in this example the right is 001 when we add one to it that changes it to a 2 using format makes it back to 002 and we will take the currdate this variable and concatenate it if it is not left date equal to currdate in other words it just rolled over to a new month then we're at November 13.
We're gonna take the currdate and reset it back to 001 and you see that's writing it back to range G1.
So, we have this, this Macro called next invoice, how do we run this.
Well, we can go to Alt+F8 you see we have next invoice here we're gonna assign it to a shortcut key maybe, yeah control+shift+N although it's funny when I was testing this I kept pressing control+N, control N, control N, I forgetting the shift key.
So, maybe that's not a good way to go maybe, what we could do instead is have a little auto shape out here just anything right you know like new invoice here it goes our auto shape, Shape Fill, No Fill and then add the text box next invoice oh that's funny it's using white, white text in there because it was expecting the fill, next invoice or else you can format this however you'd like and then finally right-click the shape assign Macro and assign it to next invoice, click OK.
All right! So, you have your your invoice ready to go to the next invoice press that Macro goes to 02, 03, 04, 05 and so on.
Let me change the system date down here just make it be November, click OK.
And now, when we do next invoice.
You see it goes to November 13001 and then 002, 003 and so on.
Hey, great idea from Rupesh if you have some sort of a interesting invoice, number you can write a tiny little macro to solve that problem.
Oh! Hey, I wanna thank Rupesh sending that question in and wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1812.
Increment the Invoice Number in the format of OCT13001.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in through YouTube by Rupesh.
Rupesh was looking at my earlier YouTube video about the next invoice number program where we just had an invoice number like 1001 and so, the Macro was just adding l what if what, if your invoice number is more complex.
For example, here it's October 2013.
So, his invoice number is Oct13 and then 001 and he wants to increment that to 2, 3, 4, 5, 6, but when we run this on November 1st have it automatically changed to Nov13001 and in January, Jan14001 and start counting up again.
Well, yeah that's possible with a little bit of VBA.
So, we're going to press...
Well, first off make sure that your file is saved as .xlsm if it's safe does .xlsx you won't be able to have a Macro.
So, say that and then Alt+F11 start with Insert, Module and we'll just create a little module here I call it next invoice first thing we're gonna save the old invoice number from range G1.value.
Now, I'm gonna go back to Excel make sure that you're wherever your invoice numbers save, this is cell G1.
You'll have to change the Macro for that and then we're going to use the left function just like in Excel to get the five left characters that'll be sort in left date and then to see what current month it is we use a VBA system variable called date and instead of the TEXT function to format it in MMMYY format we used the format function in Excel you to use text here in VBA we use format and then we have to uppercase that.
So, in Excel upper function here in VBA you Ucase.
Hey, this is great I thought this was going to be a short little Macro and I already we have two functions that are different in VBA from Excel.
So, normally we'd use upper and text here we're using Ucase and format if the left date the left 5 characters is equal to the current date, then we have to do something tricky.
We're gonna take the right of the old invoice comma three that's the sequence number add one and then format it in 000 format.
So, in this example the right is 001 when we add one to it that changes it to a 2 using format makes it back to 002 and we will take the currdate this variable and concatenate it if it is not left date equal to currdate in other words it just rolled over to a new month then we're at November 13.
We're gonna take the currdate and reset it back to 001 and you see that's writing it back to range G1.
So, we have this, this Macro called next invoice, how do we run this.
Well, we can go to Alt+F8 you see we have next invoice here we're gonna assign it to a shortcut key maybe, yeah control+shift+N although it's funny when I was testing this I kept pressing control+N, control N, control N, I forgetting the shift key.
So, maybe that's not a good way to go maybe, what we could do instead is have a little auto shape out here just anything right you know like new invoice here it goes our auto shape, Shape Fill, No Fill and then add the text box next invoice oh that's funny it's using white, white text in there because it was expecting the fill, next invoice or else you can format this however you'd like and then finally right-click the shape assign Macro and assign it to next invoice, click OK.
All right! So, you have your your invoice ready to go to the next invoice press that Macro goes to 02, 03, 04, 05 and so on.
Let me change the system date down here just make it be November, click OK.
And now, when we do next invoice.
You see it goes to November 13001 and then 002, 003 and so on.
Hey, great idea from Rupesh if you have some sort of a interesting invoice, number you can write a tiny little macro to solve that problem.
Oh! Hey, I wanna thank Rupesh sending that question in and wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
