Solved Macro for Generate next Reference Number

March 14, 2013 at 06:28:03
Specs: Windows 7
 I wish to create a purchase order form and automate a macro button to generate the PO number in a specific format of DPT-YYMM-XXX. DPT will be a 3-letter department code assigned for each different department. YY will be the last 2 digit of the year it was created. MM will be the month it was created, but I need it to be in 2 digit format. XXX will be a running number that will be in 3 digit format. The running number will only be reset to 001 at the start of the year. The number will be depended on the PO date the user entered in a cell e.g. C2. So if I create my first PO on 1-Apr-2013, the reference number will be DPT-1304-001. Then maybe the next one on 20-May-2013 the reference number will become DPT-1305-002.The problem comes when I want to automate this process but still keep the format for the numbers. I wish to create a macro assigned to a button which will generate the next PO number when the users press the button.I prepared an extra worksheet to do the workings. Where cell C2 will reference to the cell in the Purchase Order worksheet where they input the date.And I created the following formulas :In cell C5 =C7&"-"&C8&C9&"-"&C10In cell C8 =RIGHT(YEAR(C2),2)In cell C9 =LOOKUP(MONTH(C2),C14:D25]In cell C10 I wish to have the reference number which I tried to show it in 3 digit format so I put as '001

See More: Macro for Generate next Reference Number

#1
March 15, 2013 at 06:32:56
 I have some questions:1 - Where is the department code e.g. DPT coming from? Are you referencing a cell to pull in the department code?2 - Let's say you have 2 departments, ABC and DEF. Let's say they both create a PO in the same month. Would the last 3 characters still increment by 1 even though the departments are different?Are you looking for this:ABC-1303-001DEF-1303-001or this:ABC-1303-001DEF-1303-0022 - What do you have in C14:D25? In other words, what is it that you are using the LOOKUP function for?3 - Are you storing the the PO numbers some place? I'm wondering if you can reference the last PO created and simply add 1 to the last 3 characters.For example, suppose the last PO created stored in E4. Can you reference that cell and use something like the following formula? =(Build Department and Date string here)&TEXT(RIGHT(E4,3)+1,"000")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
March 23, 2013 at 20:49:17
 Hi, thanks for the help.I thought I attached a print screen of how my workings worksheet looks like, but apparently it failed to attach to this article. In answer:1) I had set it to refer to the Department Code in C7 where I will key in the 3 letter alphabet since we plan to give each dept one file each.2) The last 3 chars just need to keep increase by 1 actually since each dept will have their own file. The number will be reset after a year.3) C14:D25 is a table referencing to different period of the financial year since we don't follow calendar month, the first period 01 starts from Apr actually. So I used a lookup function.But my problem was solved. Recently I managed to find an article to add one line to my vba code to increment the value by 1 each time it runs. Range("D10").Value = Range("D10").Value + 1And another article that solved the problem to keep the reference number in 3 digits.In cell C10 I input this formula =TEXT(D10,"000")Might not be the best way, but it solves my problem using the simplest functions.In the end my [Workings] worksheet looks something like this:```C2 1-Apr-2013 C6 =C7&"-"&C8&C9&"-"&C10 C7 DPT C8 =RIGHT(YEAR(C2),2) C9 =LOOKUP(MONTH(C2),C15:D26) C10 =TEXT(D10,"000") D10 1 ```