GBKEE

¦^´_ 3# tonycho33

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim R As Integer, R1 As Integer, Rng As Range
   
4.     R = 2: R1 = 2
   
5.     Sheets("¬ö¿ýÀÉ").UsedRange.Offset(1).Clear
   
6.     Sheets("¥DÀÉ").Activate
   
7.     Do
   
8.         Set Rng = Sheets("¥DÀÉ").Range(Cells(R, "D"), Cells(R, "D").End(xlToRight)).Resize(3)
   
9.         With Sheets("¬ö¿ýÀÉ")
   
10.             .Cells(R1, "A") = Cells(R, "A")
    
11.             .Cells(R1, "B") = Cells(R, "B")
    
12.            R1 = R1 + 1
    
13.             .Cells(R1, "C").Resize(Rng.Columns.Count, 3) = Application.Transpose(Rng)
    
14.             .Cells(R1, "G").Resize(Rng.Columns.Count) = Cells(R, "C")
    
15.             R1 = R1 + Rng.Columns.Count
    
16.         End With
    
17.         R = R + 3
    
18.     Loop While Cells(R, "D") <> ""
    
19. End Sub

½Æ»s¥N½X

¤U¸ü (80.49 KB)

2011-12-15 17:26

GBKEE

¦^´_ 16# register313
¥t¤@°j°é For

1. Sub Ex2()
   
2.     Dim A As Range, i As Integer, R1 As Integer, Rng As Range
   
3.     For Each A In Sheets("¥DÀÉ").UsedRange
   
4.       A.Value = Trim(A)
   
5.     Next
   
6.     Sheets("¬ö¿ýÀÉ2").UsedRange.Offset(1).Clear
   
7.     With Sheets("¥DÀÉ")
   
8.         For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row Step 3
   
9.             Set Rng = .Range(.Cells(i, "D"), .Cells(i, "D").End(xlToRight)).Resize(3)
   
10.             With Sheets("¬ö¿ýÀÉ2")
    
11.                 R1 = .Cells(Rows.Count, "a").End(xlUp).Offset(1).Row
    
12.                 .Cells(R1, "A").Resize(Rng.Columns.Count) = Rng.Cells(1, -2)
    
13.                 .Cells(R1, "B").Resize(Rng.Columns.Count) = Rng.Cells(1, -1)
    
14.                 .Cells(R1, "C").Resize(Rng.Columns.Count, 3) = Application.Transpose(Rng)
    
15.                 .Cells(R1, "G").Resize(Rng.Columns.Count) = Rng.Cells(1, 0)
    
16.             End With
    
17.         Next
    
18.     End With
    
19. End Sub

½Æ»s¥N½X