Previous Topic Next Topic

Populate data in Excel according with n

Solved/Closed
ruidelgadoalves Posts 5 Registration date Thursday January 26, 2012 Status Member Last seen March 5, 2012 - Feb 15, 2012 at 05:19 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 7, 2013 at 10:40 AM
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 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 20, 2012 at 09:51 AM
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
0
ruidelgadoalves Posts 5 Registration date Thursday January 26, 2012 Status Member Last seen March 5, 2012
Feb 21, 2012 at 03:13 AM
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,
0
Answer 2 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 23, 2012 at 08:17 AM
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
0
ruidelgadoalves Posts 5 Registration date Thursday January 26, 2012 Status Member Last seen March 5, 2012
Mar 5, 2012 at 03:31 AM
Dear TrowaD,

Sorry for this late reply.

It work fine. everything is 100% as expected.

Thanks for your help.

Best regards,

ruidelgadoalves
0
diogogomes Posts 1 Registration date Wednesday March 6, 2013 Status Member Last seen March 6, 2013
Mar 6, 2013 at 11:07 AM
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,
0
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 7, 2013 at 10:40 AM
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response