Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

[djossh]

Hi...I'm not really sure if this can be done using excel formulas or using VBA..here is what i want to accomplish.

I have two Texbox in userform...Textbox1 and Textbox2. If I will input the value "120" in Textbox1 and "245" in Textbox2 i want the result to be arrange/distribute in this manner below


Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]120[/TD]
[TD]150[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]151[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]201[/TD]
[TD]245[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for the help. God Bless

 

[My Aswer Is This]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

Try this:

Private Sub CommandButton2_Click()
'Modified  11/9/2018  3:13:28 AM  EST
Sheet1.Cells(6, "D").Value = TextBox1.Value
Sheet1.Cells(8, "E").Value = TextBox2.Value
End Sub

 

[djossh]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

Try this:

Private Sub CommandButton2_Click()
'Modified  11/9/2018  3:13:28 AM  EST
Sheet1.Cells(6, "D").Value = TextBox1.Value
Sheet1.Cells(8, "E").Value = TextBox2.Value
End Sub

Thanks, but this does not distribute the results like what's on my sample. the number 120 to 245 will be distributed into 50's (i was trying to breakdown a Booklet in 50's)
120 - 150 (actual booklet was 101-150.. since i will be starting from 120. so the series will be 120-150 only)
151-200 (complete set of 50's)
201-245 (201 to 245 only since this are the only series used)

 

[My Aswer Is This]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

Your sample as far as I can see has 120 in row 6 column D
I see 245 in Row 8 column E

That is what my script does.

 

[My Aswer Is This]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

Your now saying:

120 to 245 will be distributed into 50's (i was trying to breakdown a Booklet in 50's)
120 - 150 (actual booklet was 101-150.. since i will be starting from 120. so the series will be 120-150 only)
151-200 (complete set of 50's)
201-245 (201 to 245 only since this are the only series used)
You never mentioned this is your first posting.
​

 

[My Aswer Is This]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Fluff]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

How about

Private Sub CommandButton1_Click()
   Dim i As Long, r As Long
   
   Range("D6").Value = Me.TextBox1.Value
   r = 6
   For i = Application.Ceiling(Me.TextBox1.Value, 50) To Me.TextBox2.Value Step 50
      Range("E" & r).Value = i
      r = r + 1
      Range("D" & r) = i + 1
   Next i
   Range("E" & r) = Me.TextBox2.Value
End Sub

 

[djossh]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

How about

Private Sub CommandButton1_Click()
   Dim i As Long, r As Long
   
   Range("D6").Value = Me.TextBox1.Value
   r = 6
   For i = Application.Ceiling(Me.TextBox1.Value, 50) To Me.TextBox2.Value Step 50
      Range("E" & r).Value = i
      r = r + 1
      Range("D" & r) = i + 1
   Next i
   Range("E" & r) = Me.TextBox2.Value
End Sub

PERFECT!!! thank you so much...God Bless

 

[Fluff]

Re: How to Auto fill (auto distribute) in Rows/Cells based on Given numbers from textbox

You're welcome