Selected Answer
Tubrak
Your code correctly populates the array but does not transfer it to your sheet.
One problem is that in your UserForm1, the text box numbers ran along 1, 2, 4 then 3, 5, 6 (so I swapped position of 3 and 4 plus retitled CommandButton1).
If you want to use an array, then better to make it a 2-D artray (with rows and columns linke your UserForm/ worksheet) and this modified code will work:
Private Sub CommandButton1_Click()
Dim i As Long, j As Long
Dim Ary(1 To 2, 1 To 3) As Variant 'make array like text box rows/columns
For i = 1 To 2 'loop array rows
For j = 1 To 3 'loop array columns
'If Me.Controls("TextBox" & i) <> "" Then
Ary(i, j) = Me.Controls("TextBox" & j + (3 * (i - 1))).Value
'End If
Next j
Next i
'find next free row
j = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
'copy array to range
ActiveSheet.Range("A" & j & ":C" & j + 1).Value = Ary
Unload Me
End Sub
where the calculation j + (3 * (i - 1)) determines the text box number based on the row and column counters (e.g. is 1=1 and j=1, it evaluates to 4)
In fact you don't need an array and con use that sum to copy cells directly to the sheet (since the speed of an array isn't needed here). This (commented) code is in the revised sheet attached:
Private Sub CommandButton1_Click()
Dim i As Long, j As Long
Dim LstRw As Long, CurrCon As String
'find last used row
LstRw = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 2 'loop userform rows
For j = 1 To 3 'loop userform columns
'recreate control name/number
CurrCon = "TextBox" & j + (3 * (i - 1))
'transfer value to sheet
Cells(LstRw + i, j) = Me.Controls(CurrCon).Value
Next j
Next i
Unload Me
End Sub
where a new variable CurrCon uses the sum as part of the string. You will see I've added a button to sheet 1 to launch the form (and added the Unload statement to the sub to close the UserForm1 after entry)
Notice that I have commented out (or not used) your test for a blank value- that's because your version would have confused the population of the array (it could skip aray cells).
What I think you need is a test to see if a row is complete but that can be done when you write the subs to calculate TextBox 3 (and 6) values from the Unit and Price text boxes (while making TextBox 3 and 6 calculated labels). In that case you will need both a price and a value anyway.
Hope this helps.
