Copy Paste Based on a Value in a cell

iteka

New Member
Joined
Sep 8, 2008
Messages
14
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

70580032.jpg


I want to have the result column.

45211248.jpg


How could I do that?

Thanks
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
I don't know of a formula for that, but this macro will do it:
Code:
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
ABCD
131515
2422115
31315
4256221
5221
6221
7221
83
956
1056
Sheet2
 

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,415
I believe this code is a little faster

Code:
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

New Member
Joined
Sep 8, 2008
Messages
14
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,205
Members
425,266
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top