How to add one to an alphanumeric string? [duplicate]

Question

I've been tasked with creating an automated Purchase Order form in Excel.
I used an automated invoice tutorial as a place to start, substituting out "invoice number" for "purchase order number".

The purchase order document has been created and my purchase number is set as P000001.
PurchaseOrderSample

I want the purchase order number to automatically increase by one each time a new PO is created.

When I run the macro I get

Run-time error'13': Type mismatch.

Sub CreateNewPO()

Dim purchnum As String 

purchnum = Range("I5")
Range("D2:E2,B9,A15:L40").ClearContents
MsgBox "Your next PO number is " & purchnum + 1
Range("I5") = purchnum +1 
Range("D2").Select

Thisworkbook.Save

I tried changing the Dim purchnum from As String to As Long.
The error moved from the MsgBox to the purchnum = Range("I5") line.

I tried changing the wording of the MsgBox to using purchase order number, purch number and a few other variations.

One way to debug is to try comment out line by line to see where exactly the problem creeps :) — Michał Turczyn
– Michał Turczyn, Commented Aug 25 at 5:32
Your PO number is a string (P000001) - you can't add 1 to a string. — Ike
– Ike, Commented Aug 25 at 5:49

taller · Accepted Answer · 2025-08-25 05:55:57Z

1

purchnum is a string, so adding a number to it (e.g., purchnum + 1) will result in error 13.

Assuming the purchase order number starts with a letter followed by digits, use Val() to extract the numeric part, increment it by one, and store it in the variable iNum. Then generate the next order number using a string function.

Microsoft documentation:

String Function

Format function

Val function

Pls try.

Sub CreateNewPO()

    Dim purchnum As String
    'purchnum = Range("I5")
    purchnum = "P0000001"
    Dim iNum As Long
    iNum = Val(Mid(purchnum, 2)) + 1
    Dim NewNum As String
    NewNum = Left(purchnum, 1) & Format(iNum, String(Len(purchnum) - 1, "0"))
    
    MsgBox "Your next PO number is " & NewNum
    
End Sub

edited Aug 25 at 5:55

answered Aug 25 at 5:50

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sab Aug 26 at 3:28

Sorry for taking so long to get back to you. Thank you so, so much for your help on this. I tried the coding you suggested and it work. To be honest, I don't fully understand what is happening but as I work with this more I know it will become clearer. I really do appreciate your help! :-)

taller Aug 26 at 5:06

U r welcome. What should I do when someone answers my question?

Collectives™ on Stack Overflow

How to add one to an alphanumeric string? [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related