Autoincrement an Alphanumeric value in VBA

[StacGuesses]

Hello, I'm trying to make a macro to autoincrement my invoice number which is a combination of letters and numbers. I'd like the last number to increase by 1 every time I run the macro.

ex. ABC-2023-01
increasing to
ABC-2023-02

1) do I need to first identify my variable as a string? or is there another data type to use?
2) if I identify it as a string do I then convert to an integer or long data type?

Current code that doesn't work

Sub CreateNewInvoice()

Dim invno As String

invno = Range("C3")

Range("B9, B19:I18").ClearContents

MsgBox "Your next invoice number is" & invno + 1

Range("B9").Select

ThisWorkbook.Save
End Sub


Thanks to all those that can help.

 

[johnnyL]

How about something like:

Sub CreateNewInvoice()
'
    Dim invno                   As String
'
    invno = Range("C3")
'
    Range("B9, B19:I18").ClearContents
'
    MsgBox "Your next invoice number is" & Left(invno, InStrRev(invno, "-")) & "0" & Mid(invno, InStrRev(invno, "-") + 1) + 1
'
    Application.Goto Range("B9")
'
    ThisWorkbook.Save
End Sub

 

[AlphaFrog]

Sub CreateNewInvoice()

Dim invno As String, s As Variant

invno = Range("C3")
s = Split(invno, "-")
s(2) = s(2) + 1
invno = s(0) & "-" & s(1) & "-" & Format(s(2), "00")

Range("B9, B19:I18").ClearContents

MsgBox "Your next invoice number is " & invno

Range("B9").Select

ThisWorkbook.Save
End Sub

 

[StacGuesses]

Thank you @AlphaFrog and @johnnyL,

@AlphaFrog, your code worked - thank you.
@johnnyL I'm concerned this will run into issues as the invoice number grows >09.

Thank you both for taking the time.

 

[johnnyL]

@johnnyL I'm concerned this will run into issues as the invoice number grows >09.

No worries. I wasn't sure if you always wanted the leading zero or not. Easily fixed.

Sub CreateNewInvoice()
'
    Dim invno   As String
'
    invno = Range("C3")
    Range("B9, B19:I18").ClearContents
    MsgBox "Your next invoice number is" & Left(invno, InStrRev(invno, "-")) & Format(Mid(invno, InStrRev(invno, "-") + 1) + 1, "00")
'
    Application.Goto Range("B9")
'
    ThisWorkbook.Save
End Sub

 

[StacGuesses]

ahh Cool.

Thank you.

I'm now struggling with a second challenge.

I have a second macro to saveAs xmlx and the code seem to work fine, but when I go to open the newly save file it is in an unrecognized formal.

Here is the code, and I've attached what the saved file looks like.

 

[johnnyL]

Glad we could help.

 

[StacGuesses]

ahh Cool.

Thank you.

I'm now struggling with a second challenge.

I have a second macro to saveAs xmlx and the code seem to work fine, but when I go to open the newly save file it is in an unrecognized formal.

Here is the code, and I've attached what the saved file looks like.

@johnnyL

Sub SaveInvoiceExcel()

Dim invTx As String
Dim cliname As String
Dim total As Currency
Dim amt As Currency
Dim GST As Currency
Dim dt_issue As Date
Dim Term As Byte
Dim desc As String
Dim commnt As String
Dim path As String
Dim Fname As String

invTx = Range("C3")
cliname = Range("B9")
total = Range("I38")
amt = Range("I36")
GST = Range("I37")
desc = Range("B18")
commnt = Range("B35")
dt_issue = Range("C4")
Term = Range("C5")
path = "C:\Golden Planet Mining\z_Personal\Stacked Geoscience\Invoices\"
Fname = invTx & " - " & cliname

'copy inv to the invoice sheet to the new worksheet
Sheet2.Copy

'delete all buttons on worksheet
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp

'save the new workbook to specified folder
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs filename:=path & Fname, FileFormat:=51
.Close
End With
'add details of invoice to the Invoice Tracking
End Sub