Hsieh

¦^´_ 1# john2006168

1. Sub Ex()
   
2. Dim Ar()
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set dc1 = CreateObject("Scripting.Dictionary")
   
5. With Sheet1
   
6.    For Each a In .Range(.[A2], .[A65536].End(xlUp))
   
7.        d(a.Value) = a.Offset(, 1)
   
8.    Next
   
9.    For Each a In .Range(.[D2], .[D65536].End(xlUp))
   
10.        dc1(a.Value) = a.Offset(, 1)
    
11.    Next
    
12. End With
    
13. Sheet2.Columns("A:E") = ""
    
14.    For Each ky In d.keys
    
15.       If d(ky) <> dc1(ky) And d(ky) <> "" Then
    
16.          ReDim Preserve Ar(s)
    
17.          Ar(s) = Array(ky, d(ky))
    
18.          s = s + 1
    
19.       End If
    
20.     Next
    
21.     If s > 0 Then Sheet2.[A1].Resize(s, 2) = Application.Transpose(Application.Transpose(Ar))
    
22.     s = 0: Erase Ar
    
23.    For Each ky In dc1.keys
    
24.       If d(ky) <> dc1(ky) And dc1(ky) <> "" Then
    
25.          ReDim Preserve Ar(s)
    
26.          Ar(s) = Array(ky, dc1(ky))
    
27.          s = s + 1
    
28.       End If
    
29.     Next
    
30.     If s > 0 Then Sheet2.[D1].Resize(s, 2) = Application.Transpose(Application.Transpose(Ar))
    
31. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 4# sping

1. Sub ¦P¤u§@ªí¤£¦PÄæ¦ì¤§¤ñ¹ï()
   
2. Dim Ar(), Ay()
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. Set d2 = CreateObject("Scripting.Dictionary")
   
6. With Sheets("¦P¤u§@ªí¤§¤ñ¹ï")
   
7. For Each a In .Range(.[A3], .[A65536].End(xlUp))
   
8.    d(a.Text) = a.Text
   
9.    d2(a.Text) = a.Text
   
10. Next
    
11. For Each a In .Range(.[B3], .[B65536].End(xlUp))
    
12.    d1(a.Text) = a.Text
    
13.    d2(a.Text) = a.Text
    
14. Next
    
15. ReDim Preserve Ar(s): Ar(s) = "¬Û¦PName": s = s + 1
    
16. ReDim Preserve Ay(k): Ay(k) = "¤£¦PName": k = k + 1
    
17. For Each ky In d2.keys
    
18.   If d.exists(ky) = True And d1.exists(ky) = True Then
    
19.   ReDim Preserve Ar(s) '¬Û¦P
    
20.   Ar(s) = ky
    
21.   s = s + 1
    
22.   Else
    
23.   ReDim Preserve Ay(k) '¤£¦P
    
24.   Ay(k) = ky
    
25.   k = k + 1
    
26.   End If
    
27. Next
    
28. Range("c3:d65536") = ""
    
29. .[c2].Resize(s, 1) = Application.Transpose(Ar)
    
30. .[d2].Resize(k, 1) = Application.Transpose(Ay)
    
31. End With
    
32. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 6# GBKEE


    ¦³¤å¦r²Å¸¹'¦s¦b
¥Î¸ê®Æ­åªRÅý'®ø¥¢§Y¥i«ì´_¥¿±`

Hsieh

­ì¦]¬O§Aªºa:dÄæªÅ®æ§t¦³¬Ý¤£¨£¦r¤¸
¥Î¸ê®Æ­åªR§â¤å¦r¿ëÃѲŸ¹¨ú®ø§Y¥i

¤U¸ü (17.78 KB)

2010-6-23 18:52

¤U¸ü (21.26 KB)

2010-6-23 18:53

Hsieh

¦^´_ 10# GBKEE
¨S¦³¥¿³W¤èªk
·í§Úµo²{¾ãÄæ·|³Q»{¬°¦³¸ê®Æ®É
¦Ó¥BCODE¨ç¼ÆŪ¨ú¤º®e«o·|¥X¿ù,´Nª¾¹D§t¦³¤£¥i¨£¦r¤¸
­n²M°£¥¦¤£¬O¥Îdelete´N¬O¥Î¸ê®Æ­åªR³Ì§Ö

Hsieh

¤£¦PÀɮ׶¡°µ¤ñ¹ï´N¥²¶·¨â­ÓÀɮ׳£¦b¶}±Òª¬ºA¤UŪ¨ú¸ê®Æ¤ñ¹ï
¤£§«±NÀɮפW¶Ç¥H«K¤F¸Ñ¸ê®Æµ²ºc