VBA: Auto-fill cells based on user defined values and quantity

[Dracius]

Hello,

First time poster, long time reader.

I'm trying to figure out a way using VBA where a user can specify values in 2 adjacent cells with a quantity in a 3rd cell, hit a button and have it insert the values entered a number of times equal to the quantity specified.

Example Input:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Product[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD="align: center"]Dog Socks[/TD]
[TD="align: center"]$2.95[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]Cat Mittens[/TD]
[TD="align: center"]$1.99[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]





Example Output:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Product[/TD]
[TD="align: center"]Price
[/TD]
[/TR]
[TR]
[TD="align: center"]Dog Socks[/TD]
[TD="align: center"]$2.95[/TD]
[/TR]
[TR]
[TD="align: center"]Dog Socks[/TD]
[TD="align: center"]$2.95[/TD]
[/TR]
[TR]
[TD="align: center"]Dog Socks[/TD]
[TD="align: center"]$2.95[/TD]
[/TR]
[TR]
[TD="align: center"]Dog Socks[/TD]
[TD="align: center"]$2.95[/TD]
[/TR]
[TR]
[TD="align: center"]Cat Mittens[/TD]
[TD="align: center"]$1.99[/TD]
[/TR]
[TR]
[TD="align: center"]Cat Mittens[/TD]
[TD="align: center"]$1.99[/TD]
[/TR]
[TR]
[TD="align: center"]Cat Mittens[/TD]
[TD="align: center"]$1.99[/TD]
[/TR]
[TR]
[TD="align: center"]Cat Mittens[/TD]
[TD="align: center"]$1.99[/TD]
[/TR]
</tbody>[/TABLE]















The actual product list has 50+ products and the prices can vary so using formulas isn't ideal. There can also be up to 30 different types of product per order. I figure the best approach is using VBA and having the output values specified by the user, then executing it based on the # entered into the quantity, and outputting the values into the "next empty cell" on the output sheet.

I made a sample workbook, but I don't see a way to attach it.

 

[Dracius]

Received the answers another board, this thread can be closed.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Submit_Click()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
For x = 2 To LastRow
Cells(x, 1).Resize(, 2).Copy
Sheets("Output").Cells(Sheets("Output").Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(Cells(x, 3)).PasteSpecial xlPasteValues
Next x
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub</code>

 

[Dracius]

Driving me crazy that I can't edit typos in my posts.

 

[Fluff]

In future, when posting questions here that you have asked elsewhere, please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Dracius]

Sorry, I'll remember that for the future.

I would edit my post with the cross-reference, but I lack permissions to edit my posts or add attachments, which is why I posted on the other forum too (it allows attachments).