Generating a conditional sequence number

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi everyone,


    I need to create a sequence number for customer return manage in my database. The sequence number should be written in Column A, with an order as below:

    -Start with the last two number of running year (example: 20 for 2020)

    -running month "04" for April, "05" for May and so on...

    -purchase platform "C" for CMI, "A" for Amazon (choose from user form)

    -and sequence number 001


    So for Amazon return it will be like: 2004-A001 and for the next Amazon return will be 2004-A002. Same for CMI return, it will be like" 2004-C001 and for the next company return will be 2004-C002.


    I have created the user form for all other data entry, but had no idea how to write code for this conditional sequence number. Any insights or feedback would be much appreciated.


    PS: I have attached my workbook here if you are interested to take a look at more details


    Thank you all in advance for your attention and helping hands!

    The problem you will find is that generating numbers from dates is that they won't be static. You would need to use TODAY() which is volatile.

    If the date is entered in a cell then you could get arts from the date but you can't get the sequence number


    I see you mention the userform so there might be a way to generate the sequence in VBA. I'll have a look at post back tomorrow.

    Which userform are you using?


    How many purchase platforms do you have?


    Will you have a sequence number for each individual purchase pltform or a continuous number?

    Hi Roy,


    Thanks for your msg. Please take a look at the first excel sheet "2C return" , then click "add new record" to open the data entry form. There is a text box with RMA number in there, and below you will find source (there are two purchasing platform: CMI & Amazon). I'm looking to create a sequence number for each individual purchase platform following my conditions listed above.


    Here is a bit more detail: the number that generated is continuous number, but only so when they fall into the same time period. Let's say when time goes from April to May, the RMA number would be like for example 2004-C006 to 2005-C001. Same principal apply when purchase platform is on Amazon, and then the only change would be letter "C" to "A" (ex.2005-A001).


    It sounds complicate and I know it might easier to just type in manually, but I'm just wondering if it is possible to do so with VBA?


    Thank you again for your time, and I'm waiting to hear back from you soon

    Which date are you using? The number can be generated in VBA., so I have used the call date and added a button to generate the RMA number.


    Check the attached workbook.


    There are a lot of improvements to your code that I could suggest but I think that is not suitable for a Forum question. Have a look some of my UserForm examples on my website and that I have posted on the Forum

    This is exactly what I'm looking for!! Thank you SOOOO much Roy for your generous help and time with me on this project. I'm aware that there are a lot of improvements to my code because most of them I learned step by step by watching different Youtube videoes. I will definitely check on your website and other forum post. Thanks for the advice!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login