# Sequential numbers in Excel 2010

Discussion in 'Business Applications' started by obsie, Dec 7, 2011.

Not open for further replies.

Joined:
Oct 3, 2004
Messages:
32
Hi all,

I am running a few 'blank' invoices with different invoice numbers on each of them to send to clients. The number goes like this: 053/12 054/12 055/12 and want it to increase each time I print. At the moment, we do it manually and it takes forever and I am sure there has to be a macro that would do this for me but no idea how to do it. I'm a beginner with excel so really need some step by step help if possible.

The numbers are in cell J2 and in cell J49 (same number but on different sides of the invoice page). I obviously want the "055" number to go up but not the /12 as this represents the year.

Thanks for your help, it is much appreciated!

obsie

2. ### scotty718

Joined:
Nov 19, 2010
Messages:
185
Yes, there is a macro that could do this, but I can help you better if you can attach a sample spreadsheet of what your invoice looks like. Once I have that, I can understand how it works and right the code to increase the counter.

3. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there,

If I understand you correctly, this should get you what you're looking for...

Code:
```Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim WS As Worksheet, vVal As Variant
On Error GoTo ExitRoutine
Set WS = Worksheets("Sheet1") 'set as desired
vVal = CLng(Left(WS.Range("J2").Value, InStr(1, WS.Range("J2").Value, "/") - 1))
WS.Range("J2").Value = "'" & String(3 - Len(vVal), "0") & vVal + 1 & "/12"
WS.Range("J49").Value = "'" & String(3 - Len(vVal), "0") & vVal + 1 & "/12"
ExitRoutine:
End Sub```
EDIT: I just realized I confined you to no more than 3 characters! Well, it would stop at 1000 anyway. Amend the last two lines of code to these to get it to go past that limit...
Code:
```    WS.Range("J2").Value = "'" & String(IIf(Len(vVal) > 3, Len(vVal), 3) - Len(vVal), "0") & vVal + 1 & "/12"
WS.Range("J49").Value = "'" & String(IIf(Len(vVal) > 3, Len(vVal), 3) - Len(vVal), "0") & vVal + 1 & "/12"```
HTH

Joined:
Oct 3, 2004
Messages:
32
Thank you so much Zach, that worked perfectly!

Is there a way to tell it to print a certain amount of them then? For example, if I want to print all the invoices from 050/12 to 150/12 or something like it... Could I do it in one go or do I need to press the print button 100 times?

Thanks again for the help, so appreciated!

obsie

5. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
I think I would add a separate routine for that, so you don't lose the functionality you currently want/have. I would change your routine to look like this (goes into a standard module):
Code:
```Option Explicit

Dim WS As Worksheet, vVal As Variant

Const sInvoice1 As String = "J2"
Const sInvoice2 As String = "J49"

Sub AddInvoiceNumber(Optional iRepeat As Long = 1, Optional iStart As Long = 0)
On Error GoTo ExitRoutine
Set WS = Worksheets("Sheet1") 'set as desired
vVal = CLng(Left(WS.Range("J2").Value, InStr(1, WS.Range("J2").Value, "/") - 1))
WS.Range(sInvoice1).Value = "'" & String(IIf(Len(vVal) > 3, Len(vVal), 3) - Len(vVal), "0") & IIf(iStart > 0, iStart, vVal + iRepeat) & "/12"
WS.Range(sInvoice2).Value = "'" & String(IIf(Len(vVal) > 3, Len(vVal), 3) - Len(vVal), "0") & IIf(iStart > 0, iStart, vVal + iRepeat) & "/12"
ExitRoutine:
End Sub

Sub IncreaseInvoiceX()
Dim vAdd As Variant, sMsg As String, iTest As Long, dTest As Double
Set WS = Worksheets("Sheet1") 'set as desired
vVal = CLng(Left(WS.Range("J2").Value, InStr(1, WS.Range("J2").Value, "/") - 1))
sMsg = "How much would you like to increase the invoice number by?  It is at " & vVal & " right now."
vAdd = InputBox(sMsg, "INCREASE INVOICE NUMBER", 1)
On Error Resume Next
On Error GoTo 0
If iTest < 1 Or iTest <> dTest Then
MsgBox "You may only use a positive whole number.  Please try again.", vbOKOnly, "ERROR!"
Exit Sub
End If
End Sub

Sub SetInvoiceNumber()
Dim vAdd As Variant, sMsg As String, iTest As Long, dTest As Double
Set WS = Worksheets("Sheet1") 'set as desired
vVal = CLng(Left(WS.Range("J2").Value, InStr(1, WS.Range("J2").Value, "/") - 1))
sMsg = "What would you like to set the invoice number to?  It is at " & vVal & " right now."
vAdd = InputBox(sMsg, "SET INVOICE NUMBER", vVal + 1)
On Error Resume Next
On Error GoTo 0
If iTest < 1 Or iTest <> dTest Then
MsgBox "You may only use a positive whole number.  Please try again.", vbOKOnly, "ERROR!"
Exit Sub
End If
End Sub```
You can call IncreaseInvoiceX whenever you want now and you will be prompted how much you want to increment your invoice number by. You can call SetInvoiceNumber whenever you want to set your invoice number to a static number manually.

You would then change your print routine in your ThisWorkbook module to this:
Code:
```Private Sub Workbook_BeforePrint(Cancel As Boolean)
End Sub```
HTH

As Seen On