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.

: pray2: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.