Copy Paste Based on a Value in a cell

[iteka]

Hi,

I am trying to find out an easy way to copy the value in C column, times a number which is specified in B colum, to D column. I probably could not tell my problem so let me show it. So I have such a data which is longer than 1000 rows

I want to have the result column.

How could I do that?

Thanks

 

[jbeaucaire]

I don't know of a formula for that, but this macro will do it:

Option Explicit

Sub MultiPaste()
'JBeaucaire (7/8/2009)
Dim LR As Long, NR As Long, i As Long, Rng As Range, cell As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("B1:B" & LR)
NR = 1

    For Each cell In Rng
        For i = 1 To cell.Offset(0, -1)
            Range("D" & NR) = cell
            NR = NR + 1
        Next i
    Next cell
Set Rng = Nothing
End Sub

Excel Workbook
	A	B	C	D
1	3	15		15
2	4	221		15
3	1	3		15
4	2	56		221
5				221
6				221
7				221
8				3
9				56
10				56
Sheet2

 

[Bill Rockenbach]

I believe this code is a little faster

Option Explicit
Sub CopyTimes()
    Dim i As Long
    Dim j As Integer
    Dim k As Long
    Dim a
    Dim b()
    Dim SumB As Long
    
    Range("C:C").ClearContents
    SumB = WorksheetFunction.Sum(Range("a1", Range("a65356").End(xlUp)))
    a = Range("a1", Range("B65356").End(xlUp))
    ReDim b(1 To SumB, 1 To 1)
    For i = 1 To UBound(a)
        For j = 1 To a(i, 1)
            k = k + 1
            b(k, 1) = a(i, 2)
        Next j
    Next i
Range("C1").Resize(k, 1).Value = b
End Sub

 

[iteka]

First of all, thank you very much, it works just perfect. I tried both and I also think Bill's solution is a little faster, thanks again.