0
Thanks

A few words of thanks would be greatly appreciated.

Excel - A macro to repeat rows and fill series




Issue


This is variation of a question that has been answered previously. Does anybody know how I can repeat rows in a spreadsheet by n number of times specified in a cell in that row AND then fill a series - so that:
Column A Column B
Peter 3
James 7
David 4


Will then produce this table:
Column A Column B Column C
Peter 3 1
Peter 3 2
Peter 3 3
James 7 1
James 7 2
James 7 3
James 7 4
James 7 5
James 7 6
James 7 7

I'd appreciate any help I can get with this.

Solution


Sub repeat()  
Dim n As Integer  
Dim counter As Integer  

Let n = 1  
Let counter = 1  
While Cells(n, 1) <> ""  
    For x = 1 To Cells(n, 2)  
        Cells(counter, 10) = Cells(n, 1)  
        Cells(counter, 11) = Cells(n, 2)  
        Cells(counter, 12) = x  
        Let counter = counter + 1  
    Next x  
n = n + 1  
Wend  

End Sub  

Note that


Solved by RayH on the forum.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

This document, titled « Excel - A macro to repeat rows and fill series », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments