Incrementing Alphanumeric Invoice Number

[Domenic]

Hi,

I'm currently using the following macro to increment the invoice number
for my invoice template:

Counter1 = Range("H8").Value
Counter1 = Counter1 + 1
Range("H8").Value = Counter1

I'd like to change the numbering for these invoices so that they begin
with two letters, for example, AA0001, AA0002, etc.

How can the above code be modified so that it would increment the
alphanumeric invoice number accordingly?

Thanks!

 

[JE McGimpsey]

I'm currently using the following macro to increment the invoice number
for my invoice template:

Counter1 = Range("H8").Value
Counter1 = Counter1 + 1
Range("H8").Value = Counter1

I'd like to change the numbering for these invoices so that they begin
with two letters, for example, AA0001, AA0002, etc.

How can the above code be modified so that it would increment the
alphanumeric invoice number accordingly?

One way:

Assuming that only AA0000 to AA9999 are valid (i.e, the prefix doesn't
increment):

Dim sCounter As String
Dim nCounter As Long
sCounter = Range("H8").Value
nCounter = Mid(sCounter, 3) + 1
If nCounter < 10000 Then
Mid(sCounter, 3) = Format(nCounter, "0000")
Else
sCounter = "Number out of range"
End If
Range("H8").Value = sCounter

 

[Domenic]

Hi JE,

Thank you sooooo much for your help! I had wondered if it was possible,
since the invoice number included alpha characters. Thankfully, I see
it is. That's great!

Thanks again for your help!

 

[Bob Greenblatt]

Hi,

I'm currently using the following macro to increment the invoice number
for my invoice template:

Counter1 = Range("H8").Value
Counter1 = Counter1 + 1
Range("H8").Value = Counter1

I'd like to change the numbering for these invoices so that they begin
with two letters, for example, AA0001, AA0002, etc.

How can the above code be modified so that it would increment the
alphanumeric invoice number accordingly?

Thanks!

Or, you can format cell H8 as "AA"00000

 

[Domenic]

Hi Bob,

Just tried it, and that works as well.

Thanks very much!