Invoice Macro Problem

[aussieExcel]

I have a very simple macro that I found on the internet that increases an invoice number by 1.

Sub NextInvoice()

Range("L1").Value = Range("L1").Value + 1

End Sub

It works great, provided the invoice number is just numbers. But I want to be able to add PF as in PF101425, on some of the invoices, not every invoice, however when I put the PF there the macro does not work.

There is more to the macro than just increasing the invoice number. It also populates other cells on a separate sheet with the information from the invoice and then clears all fields ready for the next invoice. So when I click on the button that triggers the macro the fields are populated correctly, even with the PF in front of the number, it is just the invoice number does not increase by 1.

If anybody can help with this problem it would be appreciated. Thank you.

[HaHoBe]

Hi, aussieExcel,

you should assign the prefix to a different cell and concatenate those values for the invoice.

Sub NextInvoice()

Const cstrPRE As String = "PF"

With Range("L1")
  If IsNumeric(.Value) Then
    .Value = .Value + 1
  Else
    .Value = cstrPRE & Mid(.Value, Len(cstrPRE) + 1) + 1
  End If
End With

End Sub

Please note that you should wrap your procedure with code-tags when showing it here on ExcelForum.

Ciao,
Holger

[aussieExcel]

Hello Holger,

Thank you. It works perfectly. Sorry about not using the code tags.


Kathleen

[jolivanes]

I was thinking more like

Sub Maybe_This()
    Dim invnr As Long, rep As Byte
    If Left([L1], 2) = "PF" Then
        invnr = Mid([L1], 3, 15)    'Max 17 characters for invoice number
    Else
        invnr = [L1]
    End If
    rep = MsgBox("Do you want PF with the invoive number?", vbYesNo + vbCritical)
    If rep = vbYes Then
        [L1] = "PF" & invnr + 1
    Else
        [L1] = invnr + 1
    End If
End Sub

This will give you the option of using "PF" or not (on some of the invoices, not every invoice)