Conditional Copy in Excel

I have a list of part numbers, and the associated onhand quantities for these numbers in the adjacent cell. I need to create as many copies of each part number as there are onhand quantities in a list, including all of the part numbers. So if there are 3 of part 123, I need the column to have 123, 123, and 123 in each corresponding row below the heading (A2, A3, and A4). If the next part number in the original list is 456 and there are 5 on hand, I need the rows A5, A6, A7, A8 and A9 to have 456 in them, and so on. Is this possible?

Vishesh's picture

Conditional Copy in Excel

Sub RunIt()
Dim rng As Range
Dim arr1
Dim x As Integer
Dim y As Integer
Dim c As Integer

'Set this range as per your requirement
Set rng = Sheet1.Range("A1:B3")

arr1 = rng
ReDim arr2(Application.WorksheetFunction.Sum(rng.Columns(2)) - 1, 1)

y = 0
For x = LBound(arr1, 1) To UBound(arr1, 1)
For c = 1 To arr1(x, 2)
If c = 1 Then
arr2(y, 1) = arr1(x, 2)
End If
arr2(y, 0) = arr1(x, 1)
y = y + 1
Next c
Next x

rng.Offset(, 3).Resize(UBound(arr2, 1) + 1, UBound(arr2, 2) + 1).Value = arr2
End Sub