CODEWITHSUNDEEP
vbaexcel
YellowSkin
YellowSkin

Reputation: 25

Excel VBA || Loop through numbered userform elements

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

Upvotes: 0

Views: 68

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

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).

Upvotes: 1

Domenic
Domenic

Reputation: 8104

Try the following...

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

Upvotes: 2

Related Questions