Populate data in Excel according with n [Solved/Closed]

Report
Posts
5
Registration date
Thursday January 26, 2012
Status
Member
Last seen
March 5, 2012
-
TrowaD
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
-
Hello all,

My intention is populate an Excel sheet with a variable number of rows according with a number inserted on a fixed column.


column A B C D
r1 2009096 1001 19 5
r2 2009097 1002 12 2
r3 2009097 1003 36 6
r4 2009099 1004 10 3
r5 2009099 1005 11 3
r6 2009099 1006 12 2
r7 2009099 1007 13 4
r8 2009101 1008 25 5
r9 2009102 1009 19 5


According with the numbers inserted in column D, i intend to obtain the following result:

column A B C
r1 2009096 1001 19
r2 2009096 1001 19
r3 2009096 1001 19
r4 2009096 1001 19
r5 2009096 1001 19
r6 2009097 1002 12
r7 2009097 1002 12
r8 2009097 1003 36
r9 2009097 1003 36
r10 2009097 1003 36
r11 2009097 1003 36
r12 2009097 1003 36
r13 2009097 1003 36
.
.
.

Would you be so kind to help me on this venture.

Many thanks.

Regards to all.

3 replies

Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Ruidelgadoalves,

Assuming that data is in sheet1, starting at row 1 (no header).
Result will be displayed in sheet2.

Correct sheet names and make backup of file first, then see if this macro yield the desired result:
Sub Test()
Dim lRow, lRow2, x, y As Integer
lRow = Sheets("sheet1").Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Sheet1").Range("E1:E" & lRow)
x = cell.Value
y = 0
    Do
Sheets("Sheet1").Select
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy
lRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Sheet2").Range("A" & lRow2).PasteSpecial
y = y + 1
    Loop Until x = y
Next cell
End Sub

Best regards,
Trowa
ruidelgadoalves
Posts
5
Registration date
Thursday January 26, 2012
Status
Member
Last seen
March 5, 2012

Hi Trowa,

Thank you for your effort, but it didn't solved my problem.

I changed the sheets names to Folha2 and Folha3 to suit my workbook.

In Folha 2 I have:

2009096 1001 19 5
2009097 1002 12 2
2009097 1003 36 6
2009099 1004 10 3
2009099 1005 11 3
2009099 1006 12 2
2009101 1007 13 4
.
.
.

With the macro you presented me, I obtained (after Esc and End):


2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
.
.
.
(and keep repeating the same row content)

What I realy wanted was:

2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009096 1001 19 5
2009097 1002 12 2
2009097 1002 12 2
2009097 1003 36 6
2009097 1003 36 6
2009097 1003 36 6
2009097 1003 36 6
2009097 1003 36 6
2009097 1003 36 6
2009099 1004 10 3
2009099 1004 10 3
2009099 1004 10 3
2009099 1005 11 3
2009099 1005 11 3
2009099 1005 11 3
.
.
.

Thank you again for all your help on this.

Best regards,
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Sorry Ruidel, I thought the column with R1...R9 was data too (silly me), but I see now that those are to indicate rows.

Try this code:
Sub Test()
Dim lRow, lRow2, x, y As Integer
lRow = Sheets("Folha 2").Range("D" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Folha 2").Range("D1:D" & lRow)
x = cell.Value
y = 0
    Do
Sheets("Folha 2").Select
Range(Cells(cell.Row, "A"), Cells(cell.Row, "C")).Copy
lRow2 = Sheets("Folha 3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Folha 3").Range("A" & lRow2).PasteSpecial
y = y + 1
    Loop Until x = y
Next cell
End Sub

Sorry for the inconvenience.

Best regards,
Trowa
ruidelgadoalves
Posts
5
Registration date
Thursday January 26, 2012
Status
Member
Last seen
March 5, 2012

Dear TrowaD,

Sorry for this late reply.

It work fine. everything is 100% as expected.

Thanks for your help.

Best regards,

ruidelgadoalves
diogogomes
Posts
1
Registration date
Wednesday March 6, 2013
Status
Member
Last seen
March 6, 2013

Hi, I am trying to do exactly the same with my data set but when running the macro I receive an error message "Run time error 9".
Any help on what I am doing wrong?
(i also want to populate according to column d)
column A B C D
00651R 103 1 10
00977B 80 0 7
01116A 58 0 6
01215C 66 0 7
01415K 83 1 9
02124E 67 1 8
04077I 65 0 5
09320Q 48 0 4
11135K 46 0 5
13105L 122 0 7


Thank you so much
Best regards,
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Diogogomes,

You will need to adjust the sheet names.
Change "Folha 2" to the name of your source sheet.
Change "Folha 3" to the name of your destination sheet.

If you don't like the screen flickering you can start (below first line) the code with
Application.ScreenUpdating = False
and end (before last line) it with
Application.ScreenUpdating = True

Best regards,
Trowa