Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Copying data records by a certain number of time using VBA

Posted on 2014-11-13
6
148 Views
Last Modified: 2014-11-13
Dear Experts:

A copying macro should perform the following tasks:

Run from C2 till the last filled cell in Column C
Take the number of the individual cells in Column C as the number of copies by which the data record is to be copied down (-1)
For example the 'Scissor data' should be listed three times, the Spoon record is to be listed 4 times and the fork record is to be listed twice after running the macro:

Before running macro:

Column A        Column B              Column C
Item_No          Description              Number
17-54               Scissor                            3
17-55               Spoon                            4
17-56               Fork                               2


After running macro:

Item_No      Description      Number
17-54           Scissor                3
17-54           Scissor                3
17-54           Scissor                3
17-55           Spoon                 4
17-55           Spoon                 4
17-55           Spoon                 4
17-55           Spoon                 4
17-56           Fork                     2
17-56           Fork                     2


Help is much appreciated. Thank you very much in advance.

I have attached a sample file for your convenience.

Regards, Andreas

Replicate-Data-Records-using-VBA.xlsm
0
Comment
Question by:AndreasHermle
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 165 total points
ID: 40439731
Please find attached.

So you can extend columns A-C, I have put the results into column E-G.
Replicate-Data-Records-using-VBA.xlsm
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
ID: 40439737
Try this:
Sub Replicator()
    Dim vData
    Dim LR                    As Long
    Dim x                     As Long
    Dim y                     As Long
    Dim j                     As Long
    Dim counter               As Long
    Dim vOut()

    LR = Cells(Rows.Count, "C").End(xlUp).row

    vData = Range("A2:C" & LR).Value
    ReDim vOut(1 To Application.Sum(Range("C2:C" & LR)), 1 To UBound(vData, 2))
    counter = 1
    For x = LBound(vData, 1) To UBound(vData, 1)
        For j = 1 To vData(x, 3)
            For y = LBound(vData, 2) To UBound(vData, 2)
                vOut(counter, y) = vData(x, y)
            Next y
            counter = counter + 1
        Next j
    Next x
    Range("A2").Resize(UBound(vOut, 1), UBound(vOut, 2)).Value = vOut
End Sub

Open in new window

0
 
LVL 35

Accepted Solution

by:
Kimputer earned 168 total points
ID: 40439750
All different code doing the same thing I see :) All finished around the same time too:

Sub test()

UsedRange = ActiveSheet.UsedRange.Rows.Count

For i = UsedRange To 2 Step -1
    j = ActiveSheet.Cells(i, 3)
    For k = j - 1 To 1 Step -1
        Set Rng = ActiveSheet.Range("C" & i).EntireRow
        Rng.Copy
        Rng.Offset(1).Insert Shift:=xlDown
    Next
Next


End Sub

Open in new window


I just went for simplicity and readability (somewhat).
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:AndreasHermle
ID: 40439767
Whow, I am impressed by the speed you answered. Will do some testing shortly and then let you know.

Thank you so much.

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 40439947
Great job from all of you. All of them work. I am really glad and you saved my day.
0
 

Author Closing Comment

by:AndreasHermle
ID: 40439957
Thank you very much for your swift and professional help. You saved me lots and lots of time. What a forum!!! ;-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question