Pls help how to Add multiple rows with same value of combobox through userform

[Sany_s11]

I have created a userform
Which have a combobox and a text box, save button.

Now when i select a value say"ABCD" in combo box, i will mention say number as "5".
Now when i click on save button, then 5 rows of value "ABCD" should be created in excel cells in the column i specify. And next time i select any other value or the same value it should repeat for the next available cell in the same column. and so on.

Please help with the code in VB to achieve this.

 

[My Aswer Is This]

So you want to always enter the value 5 times?
If not how do you plan to tell the script how many times?

And you said:

excel cells in the column i specify

How do you plan to specify what column?
​

 

[My Aswer Is This]

Assuming you want a Inputbox to ask how many times.
And assuming you want these values entered into column A

And assuming you plan to select the values from a list of values in the ComboBox1

Try this:

Put this script in the ComboBox

Private Sub ComboBox1_Change()
'Modified  11/10/2019  8:21:44 AM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim ans As Long
ans = InputBox("How Many Times")
Cells(Lastrow, 1).Resize(ans).Value = ComboBox1.Value
End Sub

 

[Sany_s11]

Thank you, it did help by adding rows as expected. But i don`t want a new dialog box to pop up. I have both combobox and input box in the same user form. So i have added a text field below the combobox, after selecting the value in the combobox i will have to mention the number in the text box and once i click save button in the same user form the values should be populated in cells.

 

[Fluff]

How about

Private Sub CommandButton1_Click()
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Me.TextBox1.Value).Value = Me.ComboBox1.Value
End Sub

 

[Sany_s11]

How about

Private Sub CommandButton1_Click()
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Me.TextBox1.Value).Value = Me.ComboBox1.Value
End Sub

Superb, It worked.... Thanks a lot. I am a new learner may be need help for any more queries.. Thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback