+ Reply to Thread
Results 1 to 9 of 9

Generate Next Sequential Number

  1. #1
    Registered User
    Join Date
    09-09-2014
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    12

    Arrow Generate Next Sequential Number

    Hello,

    I am writing a VBA script that will insert a new row then in column A generate a unique ID which is the next sequential number. The problem I am running into is that at the end of the next sequential number I was the letter 'A' to appear. IE: 4A, 5A, 6A...

    I have my script working to generate the next sequential number, but for the life of me cannot figure out how to get the letter 'A' to appear at the end.

    Any help would be greatly apprciated!!!!

    My code is attached.

    2019-12-11_9-38-42.png
    Attached Files Attached Files
    Last edited by Habs21; 12-11-2019 at 10:41 AM. Reason: Adding Code

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Generate Next Sequential Number

    .
    Not having your macro or formula to view ... try adding this to the end of the line that creates the sequential number :

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-09-2014
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Generate Next Sequential Number

    I couldn't figure out how to add the code to my post, so I just attached a TXT document with my code.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Generate Next Sequential Number

    .
    In the Response Window at the bottom of this page, click on the HASH symbol. That will create two HTML codes:

    [ CODE ]
    Paste your code here.
    [ /CODE ]

    Paste your code between the two.

  5. #5
    Registered User
    Join Date
    09-09-2014
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Generate Next Sequential Number

    When I add & "A" at the end of the line that genetaes the next sequential number, it just generates the same number 1A on everyline I add.
    It does not go to the next sequential number 2A, 3A...etc

    FYI when I add the code I get the a error from the Sucuri Firewall saying my post is being blocked because of an SQL injectino in my post.

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Generate Next Sequential Number

    .
    Habs21

    One method to accomplish your goal is to create a "helper column". You will continue to use Column A as the 'active column for creating the incrementing numbers.
    Then, using Column B as the actual display column for the combination of the incrementing number and the "A" .... you can use this change to your existing macro :


    In this manner, Column B will display the incrementing serial number plus the letter "A" at the end.

    You can hide Column A so only Column B is viewable.


    [The Forum server must be acting up as it will not accept use of the [ CODE ] [ /CODE ] menu selection. Normally the FORUM won't accept any HTML markup language except the menu selections.
    Moderators take note.)
    Attached Files Attached Files

  7. #7
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Generate Next Sequential Number

    I would say it is a bit long code for a simple task..

    try if this works for you

    Please Login or Register  to view this content.
    it will search column 1 (A) for the last row,
    then remove the last position of the last value and add 1 to the number and add A again
    this will work as long as long as there is only one letter and in the last position.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Generate Next Sequential Number

    Since the code is about inserting a row we can't be sure that the last row has the highest value.

    Therefore we need to loop the A-column to get the MaxValue.

    Sorry, but had to put the code in a textfile because site firewall is acting up.
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Generate Next Sequential Number

    Quote Originally Posted by Habs21 View Post
    Hello,
    The problem I am running into is that at the end of the next sequential number I was the letter 'A' to appear. IE: 4A, 5A, 6A...
    Try
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automatically Generate Sequential Number in Following Row
    By Det2120 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2017, 10:59 PM
  2. VBA to generate sequential number
    By asafebass in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-26-2016, 09:44 PM
  3. [SOLVED] generate sequential Number when a sheet is inserted
    By taralmehta15 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2015, 03:28 PM
  4. Generate Sequential number batches
    By ciag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 10:30 AM
  5. How to generate sequential intervals from whole number
    By Mekon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2013, 04:12 PM
  6. How to generate sequential intervals from whole number
    By Mekon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 03:59 PM
  7. Auto Generate Sequential Invoice Number
    By jneldon in forum Excel General
    Replies: 1
    Last Post: 03-09-2009, 01:17 PM

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