self generate a running number
self generate a running number
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: self generate a running number

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello
    is there a way in excel 2000 to generate a running number? eg a unique (in running order) number for purchase order form

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There are many ways to do this, depending on what event you prefer that will generate a unique number.

    Here is an example of a unique number being placed into cell A1 of Sheet1, by incrementing whatever number is in there by 1 each time the workbook opens.

    Locate the Excel workbook icon that is just to the left of the "File" worksheet menu option, near the upper left corner of your screen. Right click on that icon, then left click on View Code, and paste this in:

    Private Sub Workbook_Open()
    Sheet1.[A1] = Sheet1.[A1] + 1
    End Sub

    Modify for sheet and cell reference.

    This same effect can be accomplished by other means, such as whenever a sheet is activated, or the invoice gets printed, or a certain cell is filled with a certain value, etc. This example though, should get you started.

    Tom Urtis

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks Tom. much appreciated. do u think I can add a text to the number? eg 001/it/md.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes this can be done, and here is what I would recommend for your situation. My experience has been that most people find that it is easier to edit formulas than to edit VBA code. At least with a formula, if edited incorrectly, the worst case outcome will be a cell or cells that have error displays or incorrect calculations. Not a good scenario, but still not as bad as a VBA code that is edited incorrectly, which may stop an entire workbook in its tracks. And, there are only 3 cells totally involved here.

    So, I told you that story to tell you this one:

    Consider this suggestion -- replace the Workbook_Open code I posted originally with this instead:

    Private Sub Workbook_Open()
    Sheet1.[A1] = _
    Sheet1.[B1] & _
    "/" & _
    Sheet1.[C1]
    End Sub

    Remember, right click on the Excel workbook icon to the left of the File menu, then left click on View Code, and paste this in.

    Now, in cell B1, enter the formula
    =TEXT(LEFT(A1,3)+1,"000")

    And in C1 enter the formula
    =RIGHT(A1,LEN(A1)-SEARCH("/",A1))

    And to get off on the right foot, in cell A1 of Sheet1, enter the value
    001/it/md

    The reason for all this effort at the outset is based in my belief (and I could be mistaken but this is my experience) that having the formulas determine what you want displayed, instead of the VBA code, will be easier to maintain for most people. All this stuff can be wrapped up in VBA code, but when someone besides you must edit this file, changing the formulas might be more intuitive for them.

    Sorry for this long-winded answer, but your solution involved leading zeros, with incrementing numbers combined with text in one string. I took your reply to mean that the first 3 characters of the cell will be numerals that should increase by 1 each time the workbook opens.

    You can modify the code by referncing cells other than B1 or C1, depending on where you want toplace those formulas, preferably out of sight and protected.

    Hope this helps.

    Tom Urtis

  5. #5
    New Member
    Join Date
    Sep 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: self generate a running number

      
    Hi Tom,
    Basically i'm using excel 2007. If i want to use the same code to produce my purchase order, is there any problem for that? And how to input the code into it?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com