generating unique reference number in excel

[ssaqib]

I need excel to automatically generate a number in the following format at start up. 01/MK/FEB/2010 where the number 01 will increase with every new entry, MK will remain constant, FEB will be replaced by the first three letters of the currently running month and 2010 will be replaced with the digits for the currently running year. Any suggestions??

 

[Domski]

What happens when you get to 99?

Dom

 

[ssaqib]

lol thats a good question....but the numbers wont go over two digits in this case. The months and year also keep on changing so it stays unique

 

[Domski]

Try:

Sub test()

Dim strDate As String

strDate = UCase(Format(Date, "mmm/yyyy"))

With Sheets("Sheet1")

    If .Range("A1") = "" Or strDate <> Right(.Range("A1"), 8) Then

        .Range("A1") = "01/MK/" & strDate
    
    Else

        .Range("A1") = Format(Left(.Range("A1"), 2) + 1, "00") & "/MK/" & strDate

    End If

End With

End Sub

Dom