unique invoice number

Posted by Espey on December 06, 2001 9:04 PM

1)In the following macro, how does the prior invoice number get updated to cell Z1?

2)Is there a way to the invoice number to skip every 10th invoice number assigned

Sub GetNextInvoiceNumber()
LastInv = Worksheets("Menu").Range("Z1").value
NextInv = LastInv + 1
Worksheets("Invoice").Range("H2").value = NextInv
Worksheets("Menu").Range("Z1").value = NextInv
End Sub

Thank you in advance

Posted by Jim on December 07, 2001 6:31 AM

Hello Espey,

1) Z1 always retains the value of the last invoice number used. Each time this Sub runs, it gets incremented by 1. The Worksheets("Menu... statement places the new value in Z1. The very first time it was run, the prior invoice number would have been entered manually in Z1 (unless the first invoice was number 1).

2) Try something like this:

Sub GetNextInvoiceNumber()
LastInv = Worksheets("Menu").Range("Z1").value
If Mod(LastInv,10) = 0 then
NextInv = LastInv + 2
Else
NextInv = LastInv + 1
EndIf
Worksheets("Invoice").Range("H2").value = NextInv
Worksheets("Menu").Range("Z1").value = NextInv
End Sub

I'm no VBA guru but I think this is correct. Hope it helps.