Multi level numbering VBA function help

Question

Hello,

There's an example here of a function to create multi level numbering:

https://www.ozgrid.com/forum/forum/help-forums/excel-general/74617-automatic-dynamic-row-numbering-for-outline-numbers-like

It's exactly what I need except it only works for 3 levels. I have 5 levels. Is there anyone out there who knows of any examples of how I can achieve 5 levels of number (I like the idea of a function because I want the numbering to be dynamic.

Thanks in advance,

Mark

Answer 1

Hi,

Here is an answer for a similar problem few years ago.

Data in 5 columns (5 levels) col 2-col 6

Results in column 1 (in row 1 are headers)

pic1

result

pic2

vba macro

Sub Indexing()

'### by Rick Rothstein ###

  Dim X As Long, Z As Long, LastCol As Long, LastRow As Long

  Dim OutStr As String, Arr() As String

  Const StartCol As Long = 2

  Const StartRow As Long = 2

  Const LastUsedCol As Long = 6

  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _

            SearchDirection:=xlPrevious, LookIn:=xlValues).Row

  'LastUsedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _

                SearchDirection:=xlPrevious, LookIn:=xlValues).Column

  ReDim Arr(1 To LastUsedCol)

  For X = StartRow To LastRow

    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column

    For Z = StartCol To LastUsedCol

      If Z > LastCol Then

        Arr(Z) = ""

      ElseIf Len(Cells(X, Z).Value) Then

        Arr(Z) = Val(Arr(Z)) + 1

      Else

        Arr(Z) = Val(Arr(Z))

      End If

      Cells(X, StartCol).Offset(, -1).Value = Replace(Trim(Join(Arr)), " ", " .")

    Next

  Next

End Sub

Answer 2

Many thanks to you (and Rick), that's exactly what I needed.

Cheers,

Mark