For Loop VBA based off Text Box Value in User Form

MRN227

New Member
Joined
Dec 18, 2018
Messages
15
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



Unload Me


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
 

Some videos you may like

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.

MRN227

New Member
Joined
Dec 18, 2018
Messages
15
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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Will textbox1 always be 6 characters of which the last three will be numbers?
 

MRN227

New Member
Joined
Dec 18, 2018
Messages
15

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Does that mean the last 14 characters will be the number that should increment?
 

MRN227

New Member
Joined
Dec 18, 2018
Messages
15

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Ok how about
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
Joined
Dec 18, 2018
Messages
15
Ok how about
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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top