+ Reply to Thread
Results 1 to 8 of 8

VBA to produce a unique and incremented order number in col A

  1. #1
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    VBA to produce a unique and incremented order number in col A

    I am looking for a way in VBA to enter a unique order number value I.E. DHOrd000x or VndOrd000x into the first blank cell in column A. I already have the code to enter in the rest of the order values, just looking for a way to generate a unique order number when submitting the order onto the sheet. The code for the submission I have now is...

    Please Login or Register  to view this content.
    Can this be done in code? Any help is greatly appreciated, thank you.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to produce a unique and incremented order number in col A

    The first result of a google search yielded this:

    Please Login or Register  to view this content.
    Worked fine for me. I am assuming you know how to work with custom functions and how to assign a function to a cell value in code.
    Last edited by stnkynts; 02-26-2013 at 12:05 PM.

  3. #3
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: VBA to produce a unique and incremented order number in col A

    Does this only do random though? I need the order number to be unique, BUT incrementing. I.E. DHOrd00001, DHOrd00002, etc.

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to produce a unique and incremented order number in col A

    Please Login or Register  to view this content.
    Each time you run the code it should increase the last number by 1 as per your example. I formatted it to have up to 5 digits with leading 0's when appropriate.

  5. #5
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: VBA to produce a unique and incremented order number in col A

    I pasted the code into my userform procedures in its own proc, called it from my save code, and am getting runtime error 13, Type Mismatch at the following line

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to produce a unique and incremented order number in col A

    You might want to specify a worksheet in front of the range. Also if the value in cell A1 of that sheet is less than 5 characters it will kick out an error. Other than that hard to say without looking at the sheet.

  7. #7
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: VBA to produce a unique and incremented order number in col A

    Well that might be part of the problem, there is not a value in that cell. I am entereing in values into the rows from a userform. So there is no value in the order Number field to start with. This is what I was trying to get done in code. I did add the sheet name to your code and I am still getting the same error. I also tried adding DHORD into the cell and it also errored out. Code below

    Please Login or Register  to view this content.
    My Save Code - doing some other testing as well, so the call to this proc is commented out at the time

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA to produce a unique and incremented order number in col A

    I guess this is my bad. I assumed by your number of posts that you had some concept of how VBA works, but I was wrong. Just saying Sheet1 in front of the Range means nothing. Example: Sheet1.Range("A1") should be Sheets("Sheet1").Range("A1").

    It still works if there is no value in cell A1 by setting the first number to 1. The only time I can see it getting hung up is if there is less than 5 characters in cell A1, which we could fix with using "Len". I assumed this would not be necessary though since you are going to be adding just 1 more number each time.

    Try putting DHORD00001 into cell A1. I have tried this on a test workbook and it works for me ie becomes ?????00002. It will randomize the first 5 digits each time because it is not clear to me when you want the random part generated and when you dont. If we need to keep the first 5 letters constant that is easy to do but I need parameters on when it should stay the same and when it should change.
    Last edited by stnkynts; 02-26-2013 at 03:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1