For each loop TextBox data copy to excel

[beman]

Hi,

I'm new to VBA so I get stuck from time to time

Since a couple of days I was struggeling with the For Each Loop in combination with getting the value from textboxes in a userform and copying these to excel...

The code picks up de data entered in the userform textbox's but instead of adding them only one time for each textbox value it's adding it 91 time, that 1820 lines added to excel and I just need the text of all 20 textboxes once

Does anybody know what the problem is with my code?

Dim i As Integer
Dim TextData As String
Dim lastRow As Long


For i = 1 To 20
    For Each txtPart In Controls
        TextData = Controls("txtPart" & i).Value
        lastRow = Sheets("DePop").Range("A" & Rows.Count).End(xlUp).Row
        Sheets("DePop").Cells(lrReg + 1, "A").Value = TextData
        Next txtPart
Next i

Thanks for the help!

 

[wideboydixon]

Assuming you have txtPart1, txtPart2, ..., txtPart20 controls on your form:

Dim i As Integer
Dim lastRow As Long

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

*NB: Untested

WBD

 

[beman]

WBD!

Your the best! This works perfectly.