Excel VBA || Loop through numbered userform elements

Question

I am looking to make my already working code more efficient.

This is the code that I have and works:

rng.Parent.Cells(LastRow + 1, 8).Value = textBoxTask1.Value
rng.Parent.Cells(LastRow + 2, 8).Value = textBoxTask2.Value
rng.Parent.Cells(LastRow + 3, 8).Value = textBoxTask3.Value
rng.Parent.Cells(LastRow + 4, 8).Value = textBoxTask4.Value
rng.Parent.Cells(LastRow + 5, 8).Value = textBoxTask5.Value
rng.Parent.Cells(LastRow + 6, 8).Value = textBoxTask6.Value
rng.Parent.Cells(LastRow + 7, 8).Value = textBoxTask7.Value
rng.Parent.Cells(LastRow + 8, 8).Value = textBoxTask8.Value

This is what I am trying but doesn't seem to work.

For l = 1 To 8
    rng.Parent.Cells(LastRow + l, 8).Value = "textBoxTask" & l.Value
Next l

user6585761 · Accepted Answer · 2018-09-05 12:34:40Z

2

Try the following...

For l = 1 To 8
    Rng.Parent.Cells(LastRow + l, 8).Value = Me.Controls("textBoxTask" & l).Value
Next l

answered Sep 5, 2018 at 12:34

user6585761

Sign up to request clarification or add additional context in comments.

Comments

Darren Bartrup-Cook · Accepted Answer · 2018-09-05 12:37:22Z

1

Two ways I can think of doing it.

The first is following your For...Next loop version:

Dim l As Long
For l = 1 To 8
    Sheet1.Cells(l, 8) = UserForm1.Controls("TextBox" & l)
Next l

The second way is to do it in one hit with an array:

With UserForm1
    ThisWorkbook.Worksheets("Sheet1").Range("H1:H8") = _
        Application.Transpose(Array(.TextBox1, .TextBox2, .TextBox3, .TextBox4, _
                                    .TextBox5, .TextBox6, .TextBox7, .TextBox8))
End With

(you can remove the Application.Transpose if you're copying the values to a single row rather than column).

answered Sep 5, 2018 at 12:37

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

1 Comment

YellowSkin Over a year ago

I like the For...Next loop the most. Thank you for this.

Collectives™ on Stack Overflow

Excel VBA || Loop through numbered userform elements

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related