Generating a purchase order number incorporating month/year

[dkmiller16]

I am trying to create a form that will generate a PO#, I would like to have the first 4 numbers be the current year/month "yy/mm" followed by a 2 digit incrementing number starting with 01. For example if I create 4 PO#'s in April 2021 and then 3 in May of 2021 I would have the following.

210401
210402
210403
210404
210501
210502
210503

This will allow me to look at the PO# and determine what month/year it was created. For this purpose I will never exceed 99 PO#'s in a single month.

Thanks

 

[Logit]

Try this :

Option Explicit

Sub Test()

Dim i As Long

For i = 1 To 1
    Range("A1").Value = NewID
Next i

End Sub


Public Function NewID() As String

Dim m As String, y As String
Static inclNum As Integer
Static lastMonth As Integer

inclNum = inclNum + 1
m = Format(Date, "mm")
y = Format(Date, "yy")

If lastMonth = 0 Then lastMonth = CInt(m)

If CInt(m) <> lastMonth Then
    lastMonth = CInt(m)
    inclNum = 1
End If

NewID = CStr(inclNum)

If Len(NewID) < 2 Then NewID = "0" & NewID

NewID = y & m & NewID


End Function

 

[dkmiller16]

Try this :

Option Explicit

Sub Test()

Dim i As Long

For i = 1 To 1
    Range("A1").Value = NewID
Next i

End Sub


Public Function NewID() As String

Dim m As String, y As String
Static inclNum As Integer
Static lastMonth As Integer

inclNum = inclNum + 1
m = Format(Date, "mm")
y = Format(Date, "yy")

If lastMonth = 0 Then lastMonth = CInt(m)

If CInt(m) <> lastMonth Then
    lastMonth = CInt(m)
    inclNum = 1
End If

NewID = CStr(inclNum)

If Len(NewID) < 2 Then NewID = "0" & NewID

NewID = y & m & NewID


End Function

@Logit that is exactly what I was looking for. Thank you for that. I have done enough with vba that I am pretty sure I can incorporate that into a form to populate a field. A newb question I have though. Where is it storing the last value/number that it created. No matter if I delete A1 or open a new tab and run the macro, it knows what the next number should be.

 

[Logit]

Well .. basically it is all contained within the Public Function NewID() As String and inclNum = inclNum + 1

Incrementing the number by 1.

It does not have any code to limit the number to 99 then start over at 01 again.

You'll have to "seed" that number yourself.