Macro to Generate Complex Number/Code

**RichTea88** · 02-06-2013, 09:34 AM

Hello All,

does anyone have any ideas how to write a Macro that will generate complex numbers/code?

What I'd like is a number like this "ENER-TR-0213-001" Where the 'ENER-TR' is static, the '0213' is the month and the year and the '001' is just a simple number that will increase with every number I generate.

When generated the number will be temporarily stored in cell (R, 12) and then later shifted to another worksheet (with other data) where I store all the numbers generated in column (A, 2 - onwards)

So this macro would have something to clear the contents of the cell, generate a unique number in sequence (in the above format) and store it in Cell R12 every time I run the Macro.

The numbers can never be duplicates.

Many Thanks for any help in advance, always greatly appreciate the amount of help I get from this forum.

**Jakobshavn** · 02-06-2013, 10:16 AM

Give this macro a try:

Sub NumberMaker()
Dim R12 As Range, v As String
Set R12 = Range("R12")
v = R12.Value
If v = "" Then
    R12.Value = "ENER-TR-0213-001"
Else
    arr = Split(v, "-")
    u = UBound(arr)
    arr(u) = Format(CInt(arr(u)) + 1, "000")
    arr(u - 1) = Format(Date, "mmyy")
    R12.Value = Join(arr, "-")
End If
End Sub

It updates R12 by both the date and an increased serial number. Clearly, you must leave R12 alone after you copy it elsewhere.