Search code examples
excelvbafor-loopuserformnested-for-loop

How can I use nested For Loop for a UserForm and Cells in Excel VBA?

I've been trying to do a Userform that has 28 TextBoxes which one half of the TextBoxes are inputs for Range("J7:J20") and the other half is for Range("K7:K20"). However, the input does not show up in the Cells. Here is the code, I would appreciate any help or advice, thank you.

Private Sub ApplyButton_Click()
For i = 1 To 20
For j = 1 To 27 Step 2

Sheet1.Range("J" & CStr(i + 6)).Value = Me.Controls("TextBox" & CStr(j)).Value

Next j
Next i




End Sub
Solution 
  • Private Sub ApplyButton_Click()
    Dim c As long, r As long, s As String
    ' J7:K20 Textbox1=J7 Textbox2=K7    
    For r = 0 To 13
        For c = 1 To 2
           s = "TextBox" & r * 2 + c
           Sheet1.Cells(r + 7, c + 9).Value = Me.Controls(s).Value
        Next
    Next
End Sub