Nested FOR loop from multiple FOR loops

**Chilton** · 08-14-2012, 06:36 PM

I am completely new to the world of VB... and need some help resolving a "Compile Error: Procedure too large" problem.

I have a spreadsheet with two (2) columns of numbers: 1) Store # and 2) # of codes. I need to replicate the Store Number (Column 1) in a separate column as many times as the Number Of Codes (value in Column 2). My actual spreadsheet has 448 rows of data which must be replicate din this way.

Sample Columns:

StoreNumber Number of codes
29 75
67 75
71 125

Here's what I have created 448 times in a single Macro:

Sub Copy_Paste()
Dim i As Long
For i = 1 To Range("B2")
Range("A2").Copy
Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next i

For i = 1 To Range("B3")
Range("A3").Copy
Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next i

For i = 1 To Range("B4")
Range("A4").Copy
Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next i

End Sub

How do I create a nested FOR loop that will cycle through all 448 rows and produce approximately 44,625 cell values representing the Store Numbers replicated as many times as required by the Number of Codes column?

I really appreciate your help!

Chilton

**mike7952** · 08-14-2012, 08:05 PM

Is this what your wanting?

Sub test()
 Dim rng As Range
 Dim Cell As Range
 Dim arrStore() As Variant
 
 Set rng = Range("A2", "A" & Cells(Rows.Count, "A").End(xlUp).Row)
 ReDim arrStore(1 To 1)
 For Each Cell In rng
    For i = 1 To Range("B" & Cell.Row)
        
        arrStore(UBound(arrStore)) = Range("A" & Cell.Row)
        ReDim Preserve arrStore(1 To UBound(arrStore) + 1)
    Next i
 Next
 Range("D2").Resize(UBound(arrStore)) = WorksheetFunction.Transpose(arrStore)
End Sub

**Chilton** · 08-15-2012, 11:16 AM

Mike,

You are awesome! Thanks so much. Can I ask you a favor? Can you add comments to each line telling me what it's doing? As I said, I am COMPLETELY new to VB and would like to understand the syntax better as I use it to improve my productivity.

Thanks Again!

Chilton

**mike7952** · 08-15-2012, 11:36 AM

Sub test()
 Dim rng As Range
 Dim Cell As Range
 Dim arrStore() As Variant
 
'   Sets the Range From Column A Row 2 to the last entry in Column A
    Set rng = Range("A2", "A" & Cells(Rows.Count, "A").End(xlUp).Row)
'   Set the bounds of Array arrStore
    ReDim arrStore(1 To 1)
'   Now loop thru all the Cells in rng
    For Each Cell In rng
'      Now loop from 1 to the value in Column B
       For i = 1 To Range("B" & Cell.Row)
'          Add to Array arrStore the value from Column A
           arrStore(UBound(arrStore)) = Range("A" & Cell.Row)
'          Make room for the Next entry into Array
           ReDim Preserve arrStore(1 To UBound(arrStore) + 1)
       Next i
    Next

    Range("D2").Resize(UBound(arrStore)) = WorksheetFunction.Transpose(arrStore)
End Sub