Generating auto year in VB

[RedMisfit]

Hi

I am creating a VB form, which updates a spreadsheet, and as part of this creates a unique reference number.

I want the Ref number to be CUST/YY/unique number

I am struggling to get the year automatically, I have tried a couple of ways but it didnt work. Currently I am using the below fix, but would like the 19 to be auto generated so it will change every year. I also need to to start from 1 again when the year changes

eg if the ref was CUST/19/1234 I would like it to go to CUST/20/1 in 2020 and not just clock over to 1235. Thanks

'''defines what the variable is to refer to. _
(The row number of the last used cell in column A.)
emptyROW = Cells(Rows.Count, "A").End(xlUp).Row
lastROW = CLng(Cells(Rows.Count, "A").End(xlUp).Row) - 1

currentURN = "CUST/19" & "/" & CStr(emptyROW)

'Determine emptyRow
emptyROW = WorksheetFunction.CountA(Range("A:A")) + 1

Any assistance will be greatly appreciated

Thanks

 

[sergioMabres]

Hi
You could try with currentURN = "CUST/" & Mid(Year(Now), 3, 2) & "/" & CStr(emptyROW)
Cheers
Sergio

 

[jmacleary]

This subroutine will look at the last entry and either add 1 to the last portion, or increse the year by 1 and start back at 1. I wrote it to put the calculated next URN in column A, modify as you need. You could easily turn it into a function to return the calculated URN instead.

Sub nextURN()


lastRow = CLng(Cells(Rows.Count, "A").End(xlUp).Row)

currentYear = Mid(Cells(lastRow, "A"), 6, 2)

currentCount = Mid(Cells(lastRow, "A"), 9)


If Format(Date, "yy") <> currentYear Then
currentYear = Format(Date, "yy")
currentCount = 0
End If

nextURN = "CUST/" + currentYear + "/" + Format((currentCount + 1))
Cells(lastRow + 1, "A").Value = nextURN

End Sub

Regards
John