VBA or Formula to autofill down

[baker_89]

I have a sheet that I need to autofill down from a cell value. So item A to repeat its name 40 times starting on sequence 1 than immediately after Item B 10 times and so forth. The order of Item A-D will change as well since sometimes I may want Item B first than Item A etc.

Thanks for anyone who can assist.


[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item A[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item B[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item C[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item D[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Sequence[/TD]
[TD]Item Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

So is the value

Sequence

Now in Range("A5")
And what do you want in A6?
Would it be

1 and B6 would be ItemA​

<strike></strike>
​

 

[baker_89]

Sequence is just a title i added for that column. Looking to build a schedule for items, they run a different number of batches each day so need to be able to adjust the number of each item and even the running order of each item each time before running the code. Will likely have drop down option for cells A1:A4 to choose their order and than manually enter the run count then run the code to fill the list below out.

End Result like this,

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Apples[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running Order[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

I assume Apple is in Range A1

But I still do not know where the value 1 is

Is this Range("A8")

Or is this on another sheet

 

[Fluff]

Maybe something like

Sub FillDownWords()
   Dim Cl As Range
   
   For Each Cl In Range("A1:A4")
      Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Cl.Offset(, 1)).Value = Cl.Value
   Next Cl
   
End Sub

 

[baker_89]

That worked perfectly, thank you!

 

[Fluff]

You're welcome