How to add numbers in TextBox by using loop

Question

For a VBA code in Excel, I have 24 TextBoxs with 8 rows 3 columns. The aim is that I want the input values in the textbox to be the same in cells by using a for loop to shorten down the code

This is my code[but it is not correct]:

Dim wks As Worksheet
Dim AddNew As Range

Set wks = Sheet1

Set AddNew = wks.Range("A65356").End(xlUp).Offset(2, 0)

For i = 1 To 8
    For j = 0 To 2
    AddNew.Offset(i, j).Value = TextBox1.Text
    AddNew.Offset(i, j).Value = TextBox2.Text
    AddNew.Offset(i, j).Value = TextBox3.Text
    AddNew.Offset(i, j).Value = TextBox4.Text
    AddNew.Offset(i, j).Value = TextBox5.Text
    AddNew.Offset(i, j).Value = TextBox6.Text
    AddNew.Offset(i, j).Value = TextBox7.Text
    AddNew.Offset(i, j).Value = TextBox8.Text
    AddNew.Offset(i, j).Value = TextBox9.Text
    AddNew.Offset(i, j).Value = TextBox10.Text
    AddNew.Offset(i, j).Value = TextBox11.Text
    AddNew.Offset(i, j).Value = TextBox12.Text
    AddNew.Offset(i, j).Value = TextBox13.Text
    AddNew.Offset(i, j).Value = TextBox14.Text
    AddNew.Offset(i, j).Value = TextBox15.Text
    AddNew.Offset(i, j).Value = TextBox16.Text
    AddNew.Offset(i, j).Value = TextBox17.Text
    AddNew.Offset(i, j).Value = TextBox18.Text
    AddNew.Offset(i, j).Value = TextBox19.Text
    AddNew.Offset(i, j).Value = TextBox20.Text
    AddNew.Offset(i, j).Value = TextBox21.Text
    AddNew.Offset(i, j).Value = TextBox22.Text
    AddNew.Offset(i, j).Value = TextBox23.Text
    AddNew.Offset(i, j).Value = TextBox24.Text
    Next j

Next i


End Sub

My TextBox

Michał Turczyn · Accepted Answer · 2019-08-26 05:43:49Z

1

You can try this approach:

Sub Populate()
    Dim r As Long, c As Long, rowWidth As Long
    rowWidth = 3
    For i = 0 To 23
        'determine row and column
        r = Int(i / 3) + 1
        c = i Mod 3 + 1
        Me.Controls("textBox" & (i + 1)).Value = Cells(r, c).Value
    Next
End Sub

Note that you need adjust code a little so it will loop through desired range.

Note also that you need to place that code in userform code :)

answered Aug 26, 2019 at 5:43

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

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

1 Comment

Michał Turczyn Over a year ago

@CeeSupa You should accept the answer if it has helped you.

kevin hiew · Accepted Answer · 2019-08-26 07:43:18Z

0

you can name your textbox with the number i and j. For example for cell(1,1) or or range("A1"), you can name your textbox as "Textbox11" then when looping through u can use Controls("Textbox" & i & j). Something like this below.

'

Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

'Starting Range A1
For i = 1 To 8
    For j = 1 To 3
    'if you want to have the value from the cell to be copied to the textbox, the formula below should be the other way round.
    wks.Cells(i, j).Value = Me.Controls("Textbox" & i & j).Text
    Next j
Next i

'

edited Aug 26, 2019 at 7:43

answered Aug 26, 2019 at 5:10

kevin hiew

961 gold badge1 silver badge8 bronze badges

8 Comments

Cee Supa Over a year ago

Private Sub cmdAdd_Click() Dim wks As Worksheet Dim AddNew As Range Set wks = Sheet1 Set AddNew = wks.Range("A65356").End(xlUp).Offset(2, 0) For i = 1 To 8 For j = 0 To 2 AddNew.Offset(i, j).Value = Controls("Textbox" & i & j).Text Next j Next i End Sub

kevin hiew Over a year ago

I was just giving an idea before, but i have redited the code to start from range A1 and end at range C8.

kevin hiew Over a year ago

note that the if you want the cells' value to be in textbox then the code within the For loop should be Me.Controls("Textbox" & i & j).Text = wks.Cells(i, j).Value

Cee Supa Over a year ago

I'm sorry, but I still can't run the code that you have corrected, and I don't know how to fix it

kevin hiew Over a year ago

I just realized your code in setting the worksheets is incorrect. it should be Set wks = Worksheets("Sheet1"). Re-edited the code.

|

Collectives™ on Stack Overflow

How to add numbers in TextBox by using loop

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related