Sequential numbers in Excel 2010

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

obsie

Thread Starter
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
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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
 

obsie

Thread Starter
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
 
Joined
Jul 25, 2004
Messages
5,458
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
    iTest = CLng(vAdd)
    dTest = CDbl(vAdd)
    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
    Call AddInvoiceNumber(CLng(vAdd))
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
    iTest = CLng(vAdd)
    dTest = CDbl(vAdd)
    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
    Call AddInvoiceNumber(1, iTest)
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)
    Call AddInvoiceNumber(1, 0)
End Sub
HTH
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top