1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sequential numbers in Excel 2010

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

Thread Status:
Not open for further replies.
Advertisement
  1. obsie

    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
     
  2. scotty718

    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

    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
     
  4. obsie

    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
     
  5. Zack Barresse

    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
        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
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030095

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice