Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Automatically Number Column

    Hi,

    On my worksheet in Column D a start number is given and size/end is given in Col E. I want the results to start from Column H.
    Example: (1) Col D 350 Col E 50 (2) Col D 101001 Col E 10

    (1)So from Col H it will write the number 000350 next col I 000351 next J 000352 till it reaches Col BE 000400.
    (2)So from Col H it will write the number 101001 next col I 101002 next J 101003 till it reaches Q 101010

    It has to be 6 digits if the start number having 3 digits it will add 3 zeros before that and complete it with total 6 digits.

    Any help, please.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    foncesa,

    This code will increment the numbers in the rows starting at column H using the parameters in Col D and E. It will format each of the cells with leading zeros to have a 6 decimal number.

    HTH
    Maud

    Code:
    Public Sub FormatCells()
    Dim I As Integer, J As Integer
    LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    For I = 1 To LastRow
        Cells(I, 8) = Cells(I, 4)
        Cells(I, 8).NumberFormat = "000000"
        For J = 9 To Cells(I, 5) + 8
            Cells(I, J) = Cells(I, J - 1) + 1
            Cells(I, J).NumberFormat = "000000"
        Next J
    Next I
    End Sub
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Maudibe,

    Its incrementing the numbers in Col H, but its incrementing one 1 additional.
    I mean if the Col D starting states 1 & Col E states end 5 it starts incrementing in Col H 1, Col I 2, Col J 3, Col K 4, Col L 5, Col M 6
    This Col M 6 is additional, Please help to recify that.
    Thanks in advance.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Simple change of the 8 to a 7 in line 7

    HTH
    Maud

    Code:
    Public Sub FormatCells()
    Dim I As Integer, J As Integer
    LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    For I = 1 To LastRow
        Cells(I, 8) = Cells(I, 4)
        Cells(I, 8).NumberFormat = "000000"
        For J = 9 To Cells(I, 5) + 7
            Cells(I, J) = Cells(I, J - 1) + 1
            Cells(I, J).NumberFormat = "000000"
        Next J
    Next I
    End Sub

  5. #5
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thankyou Maud,

    I tested its working, but on 50000 rows it throws an Run-time error '6' Overflow.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    foncesa,

    Change the line:

    Dim I As Integer, J As Integer

    to

    Dim I As Long, J As Long

    Thant should resolve your issue.

    HTH,
    Maud

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Aha!
    Maud, you fell into the trap.

    Another way is to delete the line..
    Dim I As Integer, J As Integer
    ..completely!
    And then it will work. And the code is shorter.

    RG: I rest my case again. Sometimes when you Dim variables it can cause a routine to fail (if you get the Dim wrong). If you leave the Dim out, the routine works anyway.

    zeddy

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    I would have never guessed there would be 50k+ rows

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Me neither.
    I can't even figure out what it's used for either.

    zeddy

  10. #10
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,
    Its typing error 5,000 rows.
    Is it possible to add auto headers from column H onwards as S1,S2,S3....
    Auto save this sheet as d:\office\computed.txt [Text file]

    Thanks

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    foncesa,

    See if these changes are what you are looking for. The header will adjust to the columns used.

    HTH,
    Maud
    Attached Files Attached Files

  12. #12
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi.

    Fantastic Maudibe, Thanks, along with headers please Auto save this sheet at d:\office\computed.txt [tab] in text format and exit excel.

    Highly thankfull.

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    foncesa,

    See if this is what you want to accomplish
    Attached Files Attached Files

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    foncesa (2014-10-27)

Posting Permissions

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