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.