Copy Cell Multiple times depending on variable

  • Thread starter Thread starter tbarnes
  • Start date Start date
T

tbarnes

I know there is a simple solution to this but I have brain lock and
can't figure it out.

I have a list of products and a quantity amount (Column A and B)

Apples 4
Peaches 0
Bananas 2


I need some code that will start at the top and copy the product and
paste it into a blank range the amount of times the quanity dictates.

So using the example above I would get

Apples
Apples
Apples
Apples
Bananas
Bananas

Thanks Tom
 
Modify to suit
Sub copybynumber()
Columns("K").ClearContents
For Each c In Range("i1:i3")
For i = c.Offset(, 1) To 1 Step -1
lr = cells(Rows.Count, "k").End(xlUp).Row + 1
If c.Offset(, 1) > 0 Then cells(i, "K") = c
Next i
Next c
End Sub
 
I know there is a simple solution to this but I have brain lock and
can't figure it out.

I have a list of products and a quantity amount (Column A and B)

Apples 4
Peaches 0
Bananas 2

I need some code that will start at the top and copy the product and
paste it into a blank range the amount of times the quanity dictates.

So using the example above I would get

Apples
Apples
Apples
Apples
Bananas
Bananas

Thanks Tom
Click to expand...

Option Explicit
Public Sub expand()
Application.ScreenUpdating = False
Dim I As Long, J As Long, K As Long
For I = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If IsNumeric(Cells(I, "B")) Then
If Cells(I, "B").Value > 0 Then
For J = 1 To Cells(I, "B").Value
K = K + 1
Cells(K, "C").Value = Cells(I, "A").Value
Next J
End If
End If
Next I
End Sub

Puts them into column C.

Change the "C" in Cells(K, "C").Value = Cells(I, "A").Value for a
different column.
If you want the list to start on row 2 change the K to K + 1 in the
same code line.

Ken Johnson
 
I think Don meant the i in...

If c.Offset(, 1) > 0 Then cells(i, "K") = c

to be lr.

Ken Johnson
 
For the sample listing assumed in A1:B3,
you can also model it to be dynamic using formulas

In say, C1:
=IF(ROWS($1:1)>$B$1,IF(ROWS($1:1)>SUM($B$1:$B$2),IF(ROWS($1:1)>SUM($B$1:$B$3),"",$A$3),$A$2),$A$1)
Copy C1 down to cover the max expected extent (ie the max sum of numbers in
B1:B3)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
Back
Top