Fill-in Range from UserForm

[JK]

I would like to use a UserForm that allows the user to enter (for example) 1
wiget, 2 gadgets then have my procedure list that information in a range
like so:

NO ITEM
1 widget
2 gadget
3 gadget

I know this involves an array and am able to do the sequential numbering,
but have no idea how to include the correct count for the widgets and
gadgets. I would appreciate a lending hand. Thank you in advance.

Jim Kobzeff

 

[twox4s]

JK:
Where or how is the information entered on your form? Do you have
seperate text boxes for NO & ITEM or are they together? Does the user
click a command button to enter the data to your sheet? What is the
name of the sheet? Are you using named ranges? If you can answer
these questions then maybe I can help...Mark

 

[Tom Ogilvy]

Dim j as long, i as Long, k as Long

j = 0
for k = 1 to 4 Step 2
for i = 1 to clng(Userform1.controls("Textbox" & k).Text)
j = j + 1
activecell.offset(j-1,1).Value = j
activeCell.offset(j-1,2).Value = _
Userform1.controls("TextBox" & k + 1).Text
Next i
Next k

ActiveCell.offset(j,0).Select

 

[Tom Ogilvy]

Assumes the Userform is named Userform1:
1st textbox named TB1 and contains 1
2nd textbox named TB2 and contains "Widgets"
3rd textbox named TB3 and contains 2
4th textbox named TB4 and contains "Gadgets"

This worked for me:

Private Sub CommandButton1_Click()
Dim j As Long, k As Long, i As Long
j = 0
For k = 1 To 4 Step 2
For i = 1 To CLng(UserForm1.Controls("TB" & k).Text)
j = j + 1
ActiveCell.Offset(j - 1, 0).Value = j
ActiveCell.Offset(j - 1, 1).Value = _
UserForm1.Controls("TB" & k + 1).Text
Next i
Next k

ActiveCell.Offset(j, 0).Select

End Sub