+ Reply to Thread
Results 1 to 6 of 6

how to keep in standard sequential number?

  1. #1
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,837

    how to keep in standard sequential number?

    Hi.
    When you change the year in "D3" in addition, the reset sequence (year 0000).
    I need to keep the format of 4 digits (0000)

    Run my code and then I change the year in "D3" secuencia will see that is not in the standard 4-digit

    Pattern
    13-2327
    13-2328
    13-2329
    13-2330
    13-2331
    13-2332
    13-2333
    13-2334
    13-2335
    13-2336
    14-7 14-0001 if the year change the sequence must start from Zero
    14-8 14-0002 and the format 4 digits after the dash (-)
    14-9 14-0003
    14-10 14-0004
    14-11 14-0005
    14-12 14-0006
    Attached Files Attached Files
    "No xadrez nem sempre a menor distāncia entre dois pontos é uma linha reta" G. Kasparov.

    If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select b from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: how to keep in standard sequential number?

    Change this line to:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,128

    Re: how to keep in standard sequential number?

    Try modifying your code to add the red part:

    Please Login or Register  to view this content.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,837

    Re: how to keep in standard sequential number?

    Hi. OllyXLS and 6StringJazzer 6StringJazzer is online now

    But when i chnge year,

    13-0008
    13-0009
    13-0010
    16-0011 <-changed year (wrong) |should be that way 16-0001

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,128

    Re: how to keep in standard sequential number?

    The easiest way to do this is to look at the last row of the table and see if that year is different than the one in D3. I have updated your code to do that.

    Several other comments:
    1. I am not entirely sure how your data works but it looks like you are inserting a new line at the top of your data, except you are inserting at row 9, but the data starts at row 7. I don't understand why you are doing this. Is this deliberate, or an error?
    2. I would suggest that you add your new line of data to the end of the table rather than inserting in the middle and then sorting to make it the last row. This automatically adds another row to the table.
    3. I can't figure out why you set column A to 51 after adding the data, and then set it to 50.
    4. You are then sorting the data by the value in column A but your code always sets that value as 50. So it's unclear what you are trying to achieve with the sort.
    5. When assigning numbers, do not put them in quotes.
    6. You are using more steps than necessary for many operations. For example


    Please Login or Register  to view this content.
    I have made all these updates and done a lightweight test but I don't know everything you are trying to do so my testing is limited. The year is currently set to 13 with a Part Sec of 7. If you click on New Line it will add a new line for year 13 with a Part Sec of 1 at the end of the table.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,837

    Re: how to keep in standard sequential number?

    Hi 6StringJazzer

    Thank you!!!

+ 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. Replies: 10
    Last Post: 10-30-2013, 07:29 PM
  2. Sequential Number macro
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-15-2010, 05:36 PM
  3. Sequential number within excel
    By Dev in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2006, 03:00 PM
  4. [SOLVED] sequential number
    By Malissa in forum Excel General
    Replies: 3
    Last Post: 08-15-2005, 05:05 PM
  5. [SOLVED] sequential number
    By Mike W in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-12-2005, 07:06 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