# For Loop VBA based off Text Box Value in User Form

#### MRN227

##### New Member
I'm pretty new to VBA still and was looking into For Loop to acomplish the following: Within the user form there are 3 text boxes. The first is the desired text to be sent to the work sheet. The second determines how many times Box 1 is repeated. The third determines how many times the value of Box 1 is increased by +1. Can someone help shed a light on this? This is what I have so far:
Private Sub PopulateVZC_Click()
Dim iRow As Long
iRow = Sheets("Log").Range("A5000").End(xlUp).Row + 1
Set lg = Sheets("Log")

lg.Select
With ThisWorkbook.Sheets("Log")
lg.Range("A" & iRow) = Me.VZC
lg.Range("B" & iRow) = Me.QTY
lg.Range("C" & iRow) = Me.BundleCount
End With

End Sub

Private Sub UserForm_Click()
Public Function RangeToArray(rng As Range) As Variant
Dim i As Long, r As Range
ReDim VZC(1 To rng.Count)

i = 1
For Each r In rng
VZC(i) = r.BundleCount
i = i + 1
Next r

RangeToArray = VZC

End Function
End Sub

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
Can you please explain what you mean by
The third determines how many times the value of Box 1 is increased by +1

#### MRN227

##### New Member
Thank you for replying. Text Box 1 aka (VZC) is the value I want moved to the workheet. Text Box 3 will determine how many times text box 1's value is increased by one on the worksheet.
EX:
Text Box 1's value is VZC001. In text box 2 I enter "2", in text box 3 I enter "3". So the result is:
VZC001
VZC001
VZC002
VZC002
VZC003
VZC003

#### Fluff

##### MrExcel MVP, Moderator
Will textbox1 always be 6 characters of which the last three will be numbers?

#### MRN227

##### New Member

Will textbox1 always be 6 characters of which the last three will be numbers?
In actuality textbox1 will be 17 characters long where the first three digits will be VZC

#### Fluff

##### MrExcel MVP, Moderator
Does that mean the last 14 characters will be the number that should increment?

#### MRN227

##### New Member

Does that mean the last 14 characters will be the number that should increment?
Yes thats correct. In a real life senario where this will be applied, the text will appear as such: VZC00012920211120. So the first three characters are alphabetic, the second three are leading zeros, and the last 11 characters will increment.

#### Fluff

##### MrExcel MVP, Moderator
VBA Code:
``````Private Sub CommandButton1_Click()
Dim i As Long, Rws As Long

Rws = Me.TextBox2.Value
With Sheets("Log")
With .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Rws)
.Value = Me.TextBox1.Value
For i = 1 To Me.TextBox3.Value
.Offset(i * Rws) = Left(Me.TextBox1, 3) & Format(Right(Me.TextBox1, 14) + i, "00000000000000")
Next i
End With
End With
End Sub``````

#### MRN227

##### New Member
VBA Code:
``````Private Sub CommandButton1_Click()
Dim i As Long, Rws As Long

Rws = Me.TextBox2.Value
With Sheets("Log")
With .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Rws)
.Value = Me.TextBox1.Value
For i = 1 To Me.TextBox3.Value
.Offset(i * Rws) = Left(Me.TextBox1, 3) & Format(Right(Me.TextBox1, 14) + i, "00000000000000")
Next i
End With
End With
End Sub``````
This worked amazing! I owe you a cold one! Thank you

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
5
Views
64
Replies
7
Views
159
Replies
12
Views
585
Replies
9
Views
117
Replies
1
Views
222

1,127,326
Messages
5,624,021
Members
416,006
Latest member
PCaffrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back