Results 1 to 5 of 5

Thread: How to generate an alphanumeric tree changes with criteria?

  1. #1

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    How to generate an alphanumeric tree changes with criteria?

    I'm working on excel sheet template used for SAP System and I have 2 columns looks like below:

    Code:
    Column C               Column E
    Level                  Element Code
    3                      ABCD.01.01.01               
    4                      ABCD.01.01.01.01
    4                      ABCD.01.01.01.02
    4                      ABCD.01.01.01.03
    3                      ABCD.01.01.02
    4                      ABCD.01.01.02.01 'I Want to Restart Numbering Here
    4                      ABCD.01.01.02.02
    4                      ABCD.01.01.02.03
    I succeeded in level 3 to be automated in the whole sheet by Macro as below

    Code:
    Sub AutoNumber3()
    
    Dim Rng, C As Range
    Dim Lrow As Long
    Dim i As Integer
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = Worksheets("Union").Range("C2:C" & Lrow)
    i = 1
    For Each C In Rng.Cells
    If C.Value = 3 Then
    For i = 1 To i Step 1
    C.Offset(0, 2).Value = "ABCD.01.01." & i
    Next i
    End If
    Next C
    End Sub
    and I used the same for level 4 as below

    Code:
    Sub AutoNumber4()
    Dim Rng, C As Range
    Dim Lrow As Long
    Dim i As Integer
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = Worksheets("Union").Range("C2:C" & Lrow)
    i = 1
    For Each C In Rng.Cells
    If C.Value = 4 Then
    For i = 1 To i Step 1
    C.Offset(0, 2).Value = "ABCD.01.01.01" & i
    Next i
    End If
    Next C
    End sub
    I want to Restart the numbering of level 4 from 1 each time the cells values in the level column = 3 by using Do Until the next C.Value = 3, I = 1 But I can not put it correctly in the Autonumber4 procedure

    Your help is highly appreciated since this sheet may reach to 50000 or 100000 rows which is impossible to fill them manually

    Thanks, Regards

    Moheb Labib

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: How to generate an alphanumeric tree changes with criteria?

    That looks like Excel/VBA, right? You need to ask in the Office Development Forum

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to generate an alphanumeric tree changes with criteria?

    Quote Originally Posted by .paul. View Post
    That looks like Excel/VBA, right? You need to ask in the Office Development Forum
    I've asked the mods to move this thread to the appropriate forum. Please don't create a duplicate thread in the mean time.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to generate an alphanumeric tree changes with criteria?

    Moved to Office
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to generate an alphanumeric tree changes with criteria?

    you can test like
    Code:
    Dim cel As Range, sfirst As String, cnt3 As Integer, cnt4 As Integer
    sfirst = "ABCD.01.01."
    cnt3 = 0
    For Each cel In Range("c:c")   ' i just looped column till first empty cell, change if you prefer, any headers will be ignored as they don't match the criteria of 3 or 4
        If IsEmpty(cel) Then Exit For
        If cel = 3 Then
            cnt3 = cnt3 + 1
            cel.Offset(, 2) = sfirst & Format(cnt3, "00")
            cnt4 = 0
        ElseIf cel = 4 Then
            cnt4 = cnt4 + 1
            cel.Offset(, 2) = sfirst & Format(cnt3, "00.") & Format(cnt4, "00")
        End If
        
    Next



    Code:
    3		ABCD.01.01.01
    4		ABCD.01.01.01.01
    4		ABCD.01.01.01.02
    4		ABCD.01.01.01.03
    4		ABCD.01.01.01.04
    4		ABCD.01.01.01.05
    3		ABCD.01.01.02
    4		ABCD.01.01.02.01
    4		ABCD.01.01.02.02
    4		ABCD.01.01.02.03
    3		ABCD.01.01.03
    4		ABCD.01.01.03.01
    3		ABCD.01.01.04
    4		ABCD.01.01.04.01
    4		ABCD.01.01.04.02
    4		ABCD.01.01.04.03
    4		ABCD.01.01.04.04
    4		ABCD.01.01.04.05
    4		ABCD.01.01.04.06
    4		ABCD.01.01.04.07
    4		ABCD.01.01.04.08
    4		ABCD.01.01.04.09
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width