Search code examples
vbaexceltreebinary-treemulti-level

Set up numbers into MultiLevel tree


I need to generate numbers into column F, based on hierarchy (col B:D). Below is excel examaple and also result/values which should I get. enter image description here

I know only how to display values for Level1. Here is my code:

ws_c = ws1.Cells(ws1.Rows.Count, "D").End(xlUp).Row
Dim Lev1 As Long, Lev2 As Long, Lev3 As Long
Lev1 = 1
    For i = 3 To ws_c        
        If ws1.Range("B" & i).Value <> "" Then
           ws1.Range("F" & i).Value = Lev1
           Lev1 = Lev1 + 1
        End If
    Next

Solution

  • finally I did it:

    For i = 3 To ws_c
        '**************************************************************** L1
        If ws1.Range("B" & i).Value <> "" Then
           ws1.Range("F" & i).Value = Lev1
           Lev1 = Lev1 + 1
           Lev2 = 100
        End If
        '**************************************************************** L1.1
        If ws1.Range("C" & i).Value <> "" Then
            ws1.Range("F" & i).Value = Lev2 + Lev1 - 1
            Lev2 = Lev2 + 100            
            Lev3 = 10000
        End If
        '**************************************************************** L1.1.1
        If ws1.Range("D" & i).Value <> "" Then
            ws1.Range("F" & i).Value = Lev3 + Lev1 - 1 + Lev2 - 100
            Lev3 = Lev3 + 10000
        End If
    Next