Excel - A macro to repeat rows and fill series

August 2017




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.

Related


Published by aakai1056. Latest update on June 25, 2012 at 07:38 AM by aakai1056.
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).