Excel VBA: Create hierarchical list in form of "I-1, I-2, II-1, II-2" dynamically based on-CodePudding

I want to create a dynamic numbered list in the format of column "I", which will be generated by the values in B1 and B2:

The roman and the arabic numbers should be dynamic based on cell values in B1 and B2. I already tried VBA, but I could not work out the for loops to generate the correct result. Also I´m not sure how to implement the roman numbers. Here is my code so far:

Private Sub CommandButton1_Click()
Dim Roman_Number As Integer
Dim Arabic_Number As Integer

Roman_Number = Range("B1").Value
Arabic_Number = Range("B2").Value

For i = 0 To (Roman_Number - 1)
    Cells(i   1, 5).Value = "I" & "-" & (i   1)
    Next i 

End Sub

Thank you very much in advance!

CodePudding user response：

Alternatively you can use a formula if you have Excel 365:

=LET(cntArabic,B2,
     cntRoman,B1,
     s,SEQUENCE(cntArabic*cntRoman),
     MAKEARRAY(ROWS(s),1,LAMBDA(r,c,
         ROMAN(ROUNDUP(r/cntRoman,0)) & "-" & 
         IF(MOD(r,cntRoman)=0,cntRoman,MOD(r,cntRoman))  
     )))

CodePudding user response：

Did you have got Roman and Arabic mixed up? Anyways, if you are fine with formula instead:

Formula in D1:

=TOCOL(ROMAN(SEQUENCE(B1))&"-"&SEQUENCE(1,B2))

CodePudding user response：

There is a worksheet function to generate roman numbers and you will need nested loops so the code should look like this:

Option Explicit
Sub Button1_Click()
    Dim Roman_Number As Integer
    Dim Arabic_Number As Integer
    Dim row As Integer
    Dim i As Integer
    Dim j As Integer
    
    Roman_Number = Range("B1").Value
    Arabic_Number = Range("B2").Value
    
    row = 1
    
    For i = 1 To Roman_Number
        For j = 1 To Arabic_Number
            Cells(row, 5).Value = WorksheetFunction.Roman(i) & "-" & j
            row = row   1
        Next j
    Next i
End Sub

Integer is OK as long as the numbers aren't too large.