Unique order number

hsdacct · Mar 13th 2020

Hey all,

I've been fighting with this VBA code for awhile now.. and not making much headway. I'm going to explain my use case first and then I'll explain the little progress I've made lol..

What I need is a unique order number based on the date. Ideally, it would use a YYMMDD format, and then increment if there were multiple orders in the same day, though I would get by pretty happily with a date value as well (43900 or w/e).

I'm using a master worksheet that then is filled out, and saved via a VBA macro button which takes the customer name and this order number, then saves it as a new file with that information as the document name. My worksheet has an input sheet, a bunch of form sheets, and then a new sheet I want to use to track the order number.

Ok. My goal is that when a macro button in the worksheet is clicked, a new number based on the date is generated in the first blank A cell in that 9th sheet, preferably protected, so it can't be manually changed by someone who shouldn't be in there, then in B1, it would count the number of the same dates if there's duplicates, then in C1, concat those numbers together to end up with something like "2003133" if it's today's date and the 3rd time the button was clicked today, thereby having 3 instances of 3/13/20 in the A column. Then I can use that C1 reference in the main sheet to create the order number. I'd guess at some point after the order number is made, the master worksheet has to be overwritten to retain the new instance of the button being clicked and order number created. This is my dream! There are many like it, but this one is mine! I have another button that will save the master file as a new file based on the customer name and order number.

A bit of a wall of text, but I was trying to be as thorough as I can. I've gotten it to where it's selecting the new blank cell in the 9th sheet, but I can't figure out how to do anything useful with that number, save it, and then increment based on how many entries there are.. I haven't figured out how to get it to output a NOW() formatted as YYMMDD yet either..

Any advice would be greatly appreciated! I'm continuing to fiddle here.

shaundas · Mar 13th 2020

Insert following excell formula in (example) cell A1 and insert order start number example (100) in cell P1

="Order"&TEXT(TODAY(),"yymmdd")&P1

Insert following vba code in sheet module, change cell address as per your location (example P1)

You can change word "Oder" to anything else you want.

The code will generate order number using current date and pretext text at the beginning "Order" and number end the end with auto increment, your order worksheet must be save to get auto incremental number at the end.

Code

Private Sub workbook_open()
Range("P1").Value = Range("P1").Value + 1
End Sub

hsdacct · Mar 13th 2020

Thanks for that! Is there a way to have it check and reset the counter if the new date doesn't equal the old one? Part of this effort was to shorten the order number. The way it is in your example above, it will slowly grow in length over time.

shaundas · Mar 14th 2020

Sorry I am new to coding not able to figure out how to counter check.

If you want to keep order number length sorter you can remove "Order" text and order start serial number to 1, also you can reduce date format say yymm follow with serial number. it will be (20031) 2nd order will be (20032) it's less possibilities to get duplicate.

Currently this method I am using for my quotation and invoicing.

Justin Doward · Mar 14th 2020

Hi HS

See if the attached can be modified to suit:

Code

Sub OrderNumber()

Dim ws9 As Worksheet: Set ws9 = Sheet9
Dim MyRW As Long

MyRW = ws9.Cells(1, 1).End(xlDown).Row + 1

If DateDiff("d", DateValue(Now), Left(ws9.Cells(MyRW - 1, 1).Value, 5)) < 0 Then
    x = 1
Else
    x = CLng(Right(ws9.Cells(MyRW - 1, 1), Len(ws9.Cells(MyRW - 1, 1)) - 5)) + 1
End If

With ws9.Cells(MyRW, 1)

    .NumberFormat = "General"
    .Value = Format(DateValue(Now), "00000") & x

End With

With ws9.Cells(MyRW, 2)

    .NumberFormat = "dddd, mmmm dd, yyyy"
    .Value = Left(ws9.Cells(MyRW, 1).Value, 5)

End With

End Sub

Display More

ordernumber.xlsm

Cheers

Justin

trunten · Mar 14th 2020

Could you use a GUID?

eg:

Code

Function CreateGuid() As String
    Dim s As String
    s = CreateObject("Scriptlet.TypeLib").Guid
    CreateGuid = Mid(s, 2, InStr(1, s, "}") - 2)
End Function

Unique order number

Participate now!

Share

Tags