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.