loop throught textboxes and copy to sheet

Hasson-M · Oct 6th 2021

hi

I need fixing this code

Code

Private Sub CommandButton1_Click()
 Dim i As Integer
Dim lastRow As Long

lastRow = Sheets("DATA").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 4
        Sheets("DATA").Cells(lastRow + i, "A").Value = Controls("textbox" & i).Value
Next i

End Sub

t just copy in one column . each four textboxes (CODE,BRAND,TYPE,ORIGIN) should copy to columns A,B,C,D

currently the code copy to column A and just textbox1,2,3 ,4 with ignore the rest of textboxes

so I truly appreciate if anybody help.

Logit · Oct 6th 2021

Look at this example and adapt to your project :

Code

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Display More

Hasson-M · Oct 6th 2021

thanks I know this way , but if I have much more textboxes , then the code becomes very big .so I want using loop in my case

to make the code is short . did you see my file?

**royUK** · Oct 7th 2021

Try this

Code

Dim lastRow As Long

With Sheets("DATA")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range(.Cells(lastRow, 1), .Cells(lastRow, 4)).Value = Array(TextBox1, TextBox2, TextBox3, TextBox4)
End With

Hasson-M · Oct 7th 2021

royUK thanks . what are about the rest of textboxes ? when copy from the userform should be like this

(textbox1,2,3,4)=columns(A,B,C,D)

(textbox5,6,7,8)=columns(A,B,C,D)

(textbox9,10,11,12)=columns(A,B,C,D)

how can I use loop like this

for i= 1 to 12 'numbers of textboxes

for each four textboxes when I fill . should copy to COL A,B,C,D

**royUK** · Oct 7th 2021

It's not usual to have a userform fill in multiple rows like that, but I will look at it for you.

Hasson-M · Oct 7th 2021

I know .I don't find anything about this idea in the internet so far . I hope to you achieve that.

**royUK** · Oct 7th 2021

Try this

Code

Private Sub CommandButton1_Click()
    Dim iX As Integer, iY As Integer
    Dim lastRow As Long
    
    With Sheets("DATA")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        For iX = 1 To 12
            Select Case iX
                Case 1, 2, 3, 4
                    iY = iY + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
                Case 5, 6, 7, 8
                    iY = iX - 4
                    lastRow = .Cells(.Rows.Count, iY).End(xlUp).Row + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
                Case 9, 10, 11, 12
                    iY = iX - 8
                    lastRow = .Cells(.Rows.Count, iY).End(xlUp).Row + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
            End Select
        Next iX
    End With
End Sub

Display More

Hasson-M · Oct 7th 2021

royUK magnificent ! that's what I want . just I would understand theses values ix-4,ix-8 . what means ,please ?

**royUK** · Oct 7th 2021

Does this help?

Code

Private Sub CommandButton1_Click()
    Dim iX As Integer, iY As Integer
    Dim lastRow As Long
    
    With Sheets("DATA")
    ''///find the next row for textboxes 1 - 4
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        ''/// start loop through each TextBox
        For iX = 1 To 12
            Select Case iX
            ''///first 4 TextBoxes go to next empty row
                Case 1, 2, 3, 4
                ''/// iY represents the column number for each TextBox (needs to be 1-4)
                    iY = iY + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
                Case 5, 6, 7, 8
                ''/// next 4 TextBoxes go to next row, but needs to be on Column 1 so Column is calculated by deducting 4 from iX the number of the current TextBox
                    iY = iX - 4
                    ''/// calculates correct row and column to post to.
                    lastRow = .Cells(.Rows.Count, iY).End(xlUp).Row + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
                Case 9, 10, 11, 12
                '/// same as above but we need to deduct 8 to get the correct Column
                    iY = iX - 8
                    lastRow = .Cells(.Rows.Count, iY).End(xlUp).Row + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
            End Select
        Next iX
    End With
End SubPrivate Sub CommandButton1_Click()
    Dim iX As Integer, iY As Integer
    Dim lastRow As Long
    
    With Sheets("DATA")
    ''///find the next row for textboxes 1 - 4
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        ''/// start loop through each TextBox
        For iX = 1 To 12
            Select Case iX
            ''///first 4 TextBoxes go to next empty row
                Case 1, 2, 3, 4
                ''/// iY represents the column number for each TextBox (needs to be 1-4)
                    iY = iY + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
                Case 5, 6, 7, 8
                ''/// next 4 TextBoxes go to next row, but needs to be on Column 1 so Column is calculated by deducting 4 from iX the number of the current TextBox
                    iY = iX - 4
                    ''/// calculates correct row and column to post to.
                    lastRow = .Cells(.Rows.Count, iY).End(xlUp).Row + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
                Case 9, 10, 11, 12
                '/// same as above but we need to deduct 8 to get the correct Column
                    iY = iX - 8
                    lastRow = .Cells(.Rows.Count, iY).End(xlUp).Row + 1
                    .Cells(lastRow, iY).Value = Me("TextBox" & iX).Value
            End Select
        Next iX
    End With
End Sub

Display More

Hasson-M · Oct 7th 2021

yes . thanks so much

Hasson-M · Oct 7th 2021

it's solved

**royUK** · Oct 7th 2021

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

loop throught textboxes and copy to sheet

Participate now!

Share