I am looking to create a user form that would allow to give one entry multiple parameters, resulting in a table that looks like this:

Also to note, each entry should be able to have a range of parameters, anywhere from 1 to likely 10 at most. Desirably, it would also merge the empty cells with the entry, but this is not necessary. Is there a way to do this in excel without VBA? If not, where would I start with VBA.

CodePudding user response：

ok so I think the user inputs a line of information and you want to build the above table.

so, for example the input could be:

A1: Apples

B1: red, green

then another entry could be

A1: banana

A2: yellow, brown, green

the below code can handle this with titles in A5 and B5 of Fruit and Colour(s) respectively:

Sub tableCreate()

    fruit = Range("A1").Value
    colours = Range("B1").Value
    coloursArr = Split(colours, ",")
    inputRow = Range("A" & Rows.Count).End(xlUp).Row   1
    
    For x = 0 To UBound(coloursArr)
        Range("A" & inputRow).Value = fruit
        Range("B" & inputRow).Value = coloursArr(x)
        inputRow = inputRow   1
    Next x
    
    Range("A1").Clear
    Range("B1").Clear

End Sub

Hopefully this gets you started. There are some efficiency savings to be had on this if it'll be used a lot but I just wanted to show a simple solution

this link will help with putting a button on the sheet to enter the info.