lpk187

¦^´_ 2# d8722468

1. Sub ex()
   
2. Dim rng As Range
   
3. For Each rng In Range("C2:c9")
   
4. If rng = "" Then Exit Sub
   
5.     For i = 1 To rng.Value
   
6.         ro = Cells(Rows.Count, 1).End(xlUp).Row + 1
   
7.         Cells(ro, 1) = rng.Offset(, -2)
   
8.         Cells(ro, 2) = rng.Offset(, -1)
   
9.         Cells(ro, 3) = 1
   
10.     Next
    
11. 
    
12. Next
    
13. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 4# d8722468

1. Sub ex()
   
2. Dim rng As Range
   
3. Dim arr()
   
4. For Each rng In Range("C2:c9")
   
5. If rng = "" Then GoTo 99
   
6.     For i = 1 To rng.Value
   
7.         x = x + 1
   
8.         ReDim Preserve arr(1 To 3, 1 To x)
   
9.         arr(1, x) = rng.Offset(, -2)
   
10.         arr(2, x) = rng.Offset(, -1)
    
11.         arr(3, x) = 1
    
12.     Next
    
13. Next
    
14. 99:
    
15. [A2].Resize(UBound(arr, 2), 3) = Application.WorksheetFunction.Transpose(arr)
    
16. End Sub

½Æ»s¥N½X