Hsieh

¦^´_ 1# Genie
§Q¥Î¹Ï¤ù©w¦ì
Example.zip (591.8 KB)

¤U¸ü¦¸¼Æ: 30

2013-4-2 12:21

Hsieh

¦^´_ 3# Genie

1. Sub ex()
   
2. Application.ScreenUpdating = False
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With Sheet1
   
5.   For Each a In .Range(.[A2], .[A2].End(xlDown))
   
6.   mystr = Join(Application.Transpose(Application.Transpose(a.Offset(, 1).Resize(, 3))), ";")
   
7.      If d(a.Value) = "" Then
   
8.         d(a.Value) = mystr
   
9.         Else
   
10.         d(a.Value) = d(a.Value) & Chr(10) & mystr
    
11.      End If
    
12.   Next
    
13. End With
    
14. With Sheet2
    
15.   For Each pic In .Pictures
    
16.     Set a = pic.TopLeftCell.Offset(-1, 0)
    
17.     If d.exists(a.Value) = True Then
    
18.     r = a.Offset(23).Row
    
19.     Do While .Cells(r, "B") <> "" Or .Cells(r, "F") <> ""
    
20.       a.Offset(23).EntireRow.Delete
    
21.     Loop
    
22.        ar = Split(d(.Cells(a.Row, "B").Value), Chr(10))
    
23.        ar1 = Split(d(.Cells(a.Row, "F").Value), Chr(10))
    
24.        a.Offset(23).Resize(Application.Max(UBound(ar), UBound(ar1)) + 1, 1).EntireRow.Insert
    
25.        For i = 2 To 6 Step 4
    
26.          ay = Split(d(.Cells(a.Row, i).Value), Chr(10))
    
27.          For j = 0 To UBound(ay)
    
28.            For x = 0 To 2
    
29.              If x = 0 Then
    
30.                 .Cells(a.Row, i + x).Offset(23 + j) = Split(ay(j), ";")(x)
    
31.                 Else
    
32.                 .Cells(a.Row, i + x).Offset(23 + j) = Round(Val(Split(ay(j), ";")(x)), 1)
    
33.              End If
    
34.            Next
    
35.          Next
    
36.          d.Remove .Cells(a.Row, i).Value
    
37.        Next
    
38.     End If
    
39.   Next
    
40. End With
    
41. Application.ScreenUpdating = True
    
42. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 5# Genie

Split(ay(j), ";")·|¤Á³Î¥X3Ä檺¸ê®Æ¡A¦¹°}¦C¯Á¤Þ¬°0~2
²Ä¤@­Ó¤¸¯À¬O¤å¦r¡AµLªk¨Ï¥Î¼Æ­È­pºâ
©Ò¥H¡A·í²Ä¤@­Ó¤¸¯À®É´Nª½±µ¼g¤J
²Ä2~3­Ó¤¸¯À¤~¥|±Ë¤­¤J