Doesn't copy data correctly in right location into lastrow when loop multiple textboxes on userform

[abdo meghari]

Hi guys
as in the picture I have multiple textboxes on userform when I specific rows on userform should copy start from row2 and column A: G
so based on the pic 1

and when copy to sheet should be

MM with corrected CBs v03 a.xlsm
	A	B	C	D	E	F	G
1	ITEM	CODE	BRAND	TYPE	ORIGIN	COST	SELL
2	1	BSJG580120020	BS 1200R20	G580	JAP	200	250.00
3	2	BSTG580120020	BS 1200R20	G580	THI	120.00	230.00
4	3	BSJVSJ140020	BS 1400R20	VSJ	JAP	300.00	350.00
5
BRANDS

but what I got this

MM with corrected CBs v03 a.xlsm
	A	B	C	D	E	F	G	H	I
1	ITEM	CODE	BRAND	TYPE	ORIGIN	COST	SELL
2
3
4
5
6
7
8	BSJG580120020	BS 1200R20	G580	JAP	200	250.00	2.00	BSTG580120020
9	120	230	3	BSJVSJ140020	BS 1400R20	VSJ	JAP	300
10
BRANDS

and this is the code

Private Sub CommandButton1_Click()
 
Dim i As Long, j As Long
Dim LstRw As Long, CurrCon As String, ws As Worksheet
Set ws = sheet2
'find last used row
LstRw = ws.Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To 11 'loop userform rows

    For j = 1 To 7 'loop userform columns
        'recreate control name/number
        
        CurrCon = "TextBox" & j + (11 * (i - 1))
        'transfer value to sheet
        ws.Cells(LstRw + i, j) = Me.Controls(CurrCon).Value
    Next j
Next i
MsgBox "  ok"
 
End Sub

columns count on userform are 7,rows are 11
every row on userform start textboxes number consecutively :
66,67,68,69,70,71,72
73,74,75,76,77,78,79
........
how fix this chaos when copy to sheet, and how copy to sheet based on fill multiple rows on userform please?

 

[Akuini]

@abdo meghari
Try this, if it doesn't work then please share a sample workbook to test.

Private Sub CommandButton1_Click()
 
Dim i As Long, j As Long
Dim LstRw As Long, CurrCon As String, ws As Worksheet
Dim va, a As Long, b As Long, h As Long

Set ws = sheet2
'find last used row
LstRw = ws.Range("B" & Rows.Count).End(xlUp).Row

    h = 11 * 7    'columns count on userform are 7,rows are 11
    a = 1
    ReDim va(1 To 11, 1 To 7)

    For i = 1 To h 'loop userform textbox
          b = b + 1
            va(a, b) = Me.Controls(i + 65).Value   'textboxes number start at 66
              If b = 7 Then a = a + 1: b = 0
    Next i

Range("A" & LstRw + 1).Resize(UBound(va, 1), UBound(va, 2)) = va

MsgBox "  ok"
 
End Sub

 

[abdo meghari]

thanks
error invalid argument in this line

 va(a, b) = Me.Controls(i + 65).Value   'textboxes number start at 66

FILE.xlsm
BTW : based on original code should start copying from A not to B as I did it.

 

[Akuini]

Sorry, the textbox name are Textbox66, Textbox67, ...etc, right?
So, it should be:

va(a, b) = Me.Controls("Textbox" & i + 65).Value   'textboxes number start at 66

 

[abdo meghari]

it's perfect !
can you add line to clear textboxes after copy data from userform to sheet in the end of the code please?