• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel macro for invoice number

Samia67

New Member
Hello Experts,

Good day to you all. Hope you can help me with my code. I am trying to create an invoice template in excel (Yes, I know Quickbooks does it) however, this is for a voluntary organisation that is starting out with limited funds. So far so good, because I tried to search through web and I managed to come up with this code. However, should I make it increment? Should it be automatic or is there some other way. Lastly I need the invoice number to take on this format: mm-yy-Inv#. Here is the code:

Code:
Sub NextInvoice()
With Range("I3")
.NumberFormat = "00000"
Range("I3").Value = Range("I3").Value + 1
Range("A25:I38") = ClearContents


End With
End Sub

Thank you in advance,

Samia67
 
Check this..


Code:
Sub NextInvoice()
    With Range("I3")
        If Len(.Value) = 0 Then
            .Value = "INV-" & Format(Date, "mm-yy") & "-1"
        Else
            .Value = "INV-" & Format(Date, "mm-yy") & "-" & Split(.Value, "-")(3) + 1
        End If
    End With
        Range("A25:I38") = ClearContents
End Sub
 
Hello Deepak, Thank you for the quick response, however he code returns an error (see underlined line) and secondly, I DON'T WANT to include the "INV" word/letters in the number. I just want it in this format: "02-16-0016"

Code:
Sub NextInvoice()
    With Range("I3")
        If Len(.Value) = 0 Then
            .Value = "INV-" & Format(Date, "mm-yy") & "-1"
        Else
            .Value = "INV-" & Format(Date, "mm-yy") & "-" & Split(.Value, "-")(3) + 1
        End If
    End With
        Range("A25:I38") = ClearContents
End Sub

The error line is:
Code:
.Value = "INV-" & Format(Date, "mm-yy") & "-" & Split(.Value, "-")(3) + 1

Thanks
 
Change it..

.Value = format(Date, "mm-yy") & "-" & format(Split(.Value, "-")(2) + 1,"0000")
 
Hello Experts,

I must first of all apologise for taking long to reply. I got busy and been off internet for sometime.

Deepak tried to help me out with my question, however the code doesn't seem to work. Is there something I have missed in the code? Secondly, I asked up there, what problems can I run into if I'm to use WorkbookOnOpen()

Code:
Sub NextInvoice()
    With Range("I3")
        If Len(.Value) = 0 Then
            .Value = Format(Date, "mm-yy") & "-"
        Else
            .Value = Format(Date, "mm-yy") & "-" & Format(Split(.Value, "-")(2) + 1, "0000")
        End If
    End With
        Range("A25:I38") = ClearContents
End Sub

Thanks in advance,

Raymond
 
Is this how you wanted?
Code:
Sub NextInvoice()
    With Range("I3")
        If Len(.Value) = 0 Then
            .Value = Format(Date, "mm-yy") & "-"
        Else
            .Value = Format(Date, "mm-yy") & "-" & Format(Val(Split(.Value, "-")(2)) + 1, "0000")
        End If
    End With
    Range("A25:I38").ClearContents
End Sub
 
Back
Top