Loop through textbox values in userform

[NichoD]

Hello, I am trying to loop through textboxes from 1 through 24 in my userform, and print the text (if text exist) in column a. However, only the last textbox with a value is printed. Any advice?

Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

Dim rng As Range
Set rng = ws.Range("A" & lr + 1)

Dim i As Integer

For i = 1 To 24
If Controls("txtColA" & i).Value <> "" Then
rng = Controls("txtColA" & i).Value

End If
Next i

 

[dmt32]

Hi,
see if this update to your code does what you want

    Dim lr          As Long, i As Long
    Dim rng         As Range
    
    lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    Set rng = ws.Range("A" & lr)
    
    For i = 1 To 24
        With Me.Controls("txtColA" & i)
            If Len(.Value) > 0 Then rng.Cells(i, 1).Value = .Value
        End With
    Next i

You will note that I have qualified the range for your last row variable (lr) with your worksheet object variable ws which I assume you have initialized elsewhere in your code

Dave

 

[NichoD]

Hi,
see if this update to your code does what you want

    Dim lr          As Long, i As Long
    Dim rng         As Range
    
    lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    Set rng = ws.Range("A" & lr)
    
    For i = 1 To 24
        With Me.Controls("txtColA" & i)
            If Len(.Value) > 0 Then rng.Cells(i, 1).Value = .Value
        End With
    Next i

You will note that I have qualified the range for your last row variable (lr) with your worksheet object variable ws which I assume you have initialized elsewhere in your code

Dave

Thank you so much! this saved me so much time!!

 

[dmt32]

Thank you so much! this saved me so much time!!

most welcome & appreciate your feedback

Dave