excelvbaconcatenationnumber-formattingleading-zero

Concatenate two digit year with number formatted with leading zeros

I am trying to create an invoice number with VBA in excel. I want the invoice number to be displayed "YY####" where the number is formatted with leading zeros and will increase by one with every new invoice.

Here's what I have tried:

Sub Invoice()
Dim inv As Long
inv = Range("e5")
Range("e5") = Format(Now(), "yy") & inv + 1
End Sub

And that returns YY#.

I'm not sure how to fit the number format in the code so that it includes leading zeros.

Solution

  • Let's try this code:

    Sub Invoice()
    Dim inv As Long
    inv = Right(Range("e5").Value, 4)
    Range("e5").Value = Format(Date, "yy") & Format(inv + 1, "0000")
End Sub

    Start from any number.