Need VBA help for Multilevel number according to the document path

[goldfishhime]

Hi all,

I have a table lisk this:

Type	Source	Subject	Code
	L1	doc 12	001
Attachment-	L1\doc 12	doc 13	001A
Attachment-	L1\doc 12	doc 14	001B
Attachment-	L1\doc 12	doc 15	001C
	L1	doc 16	002
Attachment-	L1\doc 16	doc 17	002A
Attachment-	L1\doc 17	doc 18	002B
	L1	doc 19	003
Attachment-	L1\doc 19	doc 20	003A
Attachment-Attachment-	L1\doc 19\doc 20	doc 21	003AA
	L1	doc 22	004
	L1	doc 23	005
Attachment-	L1\doc 23	doc 24	005A
	L2	doc 25	006
	L2	doc 26	007
	L2	doc 27	008
	L2	doc 28	009
	L2	doc 29	010
	L3	doc 30	011
	L3	doc xxxx	1000
Attachment-	L3\doc xxxx	doc yyyy	1000A

I need to assign code on column D for each row according to column B, which shows the location path.
The code should increase from 001 to infinity (depends on the number of documents), some documents might contain attachment inside, and I want those attachments to be coded as Parent's code + alphabet A~Z,take doc 13 to 15 from the above table as the example, they should be coded as 001A, 001B and 001C.
And I want to apply the same logic to attachment's attachment as well (there could be over 10 layers), take doc 21 as the example, it is the attachment of attachment, so it is coded as 003AA.

My English is not good, I hope you understand what I mean and thank you all in advance.

[arnelgp]

i think you need another Column to specify the Parent Folder (level 1 folder), example:

Type	Parent Folder	Source	Subject	Code
	L1	L1	doc 12	0001
Attachment-	L1	L1\doc 12	doc 13	001A
Attachment-	L1	L1\doc 12	doc 14	001B
Attachment-	L1	L1\doc 12	doc 15	001C
	L2	L2	doc 16	0002
Attachment-	L2	L2\doc 16	doc 17	002A
Attachment-	L2	L2\doc 17	doc 18	002B
	L4	L4	doc 19	0003
Attachment-	L4	L4\doc 19	doc 20	003A
Attachment-Attachment-	L4	L4\doc 19\doc 20	doc 21	003AA
	L5	L5	doc 22	0004
	L6	L6	doc 23	0005
Attachment-	L6	L6\doc 23	doc 24	005A
	L7	L7	doc 25	0006
	L8	L8	doc 26	0007
	L9	L9	doc 27	0008
	L10	L10	doc 28	0009
	L11	L11	doc 29	0010
	L12	L12	doc 30	0011

[Grade4.2]

Try this and let me know how you go:

Sub GenerateCodes()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim code As String
    Dim counter As Long
    Dim attachmentCounter As Long


    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the name of your worksheet
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row


    counter = 1
    For currentRow = 2 To lastRow
        If InStr(ws.Cells(currentRow, "B"), "Attachment") = 0 Then
            code = Format(counter, "000")
            ws.Cells(currentRow, "D").Value = code
            attachmentCounter = 65 ' ASCII value of "A"
            counter = counter + 1
        Else
            ws.Cells(currentRow, "D").Value = code & Chr(attachmentCounter)
            attachmentCounter = attachmentCounter + 1
        End If
    Next currentRow


End Sub

Before running the macro, make sure to replace "Sheet1" with the name of your worksheet in the Set ws = ThisWorkbook.Worksheets("Sheet1") line of code.

In summary, this macro goes through each row and checks if the word "Attachment" is present in column B. If it's not present, it assigns a three-digit code to column D. If the word "Attachment" is present, it assigns the parent's code plus a letter (A, B, C, etc.) to column D. The macro considers multiple layers of attachments when generating codes.