c_c_lai

¦^´_ 4# GBKEE

1.         For Each E In D.KEYS
   
2.             .Cells(I, "A").Resize(1, UBound(D(E), 2)) = D(E)
   

½Æ»s¥N½X

§Ú°»´úªºµ²ªG¡G(Office 2010)
·í E ¶Ç¤J­È¬° "µL" ®É¡A¥¦´N·|²£¥Í "«¬ºA¤£²Å" ªº°T®§¡C
§Ú¥Î Debug ¼Ò¦¡³v¤@°õ¦æ¡AÆ[¹î¬ÛÃöÅܼƪº¦s¤J­È¤§ÅܤơA

1.         Dk(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value

½Æ»s¥N½X

·íµo²{ E.Value ²ö¦W¬° "µL' ®É¡A«Kª¾¤j¶Õ¤w¥h¡C¦ý«D¨C¦¸¦p¦¹¡A¥u­n¬O¦³¦¹²{¶H
±µ¤U¨Óªº°õ¦æ³£¬O  "«¬ºA¤£²Å"¡C ¦¹®É¡A¦A±Nµ{¦¡½X­«·sÀx¦s¡B³ÜªM¯ù¦A­«·s°õ¦æ¡A
¥¦µÎºZ¤F«K¤S¥¿±`°õ¦æ¤F¡C
³o·¥¥i¯à¬O Office ¼ç¦bªº Bug¡A ¥ç§Y E ­Èªº«¬ºA§P©w¤§°ÝÃD¡A¨Ï±o¥N¤J­È·|µLºÝ
Åܬ° "µL"¡C
P.S.  §Ú¦b ThisWorkbook¡B¥H¤Î Module1 ¤À§O°õ¦æ³£´¿µo¥Í¦¹²§ª¬¡C

c_c_lai

¦^´_ 8# GBKEE
µ´¹ï¤£¬O .Value ªº¦]¯À¡A
.Cells(cts, "A").Resize(1, UBound(Dk(E), 2)) = Dk(E) »P
.Cells(cts, "A").Resize(1, UBound(Dk(E), 2)).Value = Dk(E)
¨ä¹ê³£¬O¦Pµ¥·N¸q¡A¦]¬°¦b¤@¶i¤J For Each E In D.KEYS «á§Y°»´ú¨ì
­È¬° "µL" ªº±¡ªp  (¦¹²{¶H¤~¥O¤H¦Ê«ä¤£¸Ñ)¡C
¥u­n¨S²§ª¬µo¥Í¡A±µ¤U¦h¦¸°õ¦æ³£¬O OK¡A
¤Ï¤§¡B«h§¡¬O "«¬ºA¤£²Å"¡C²z½×¤W¬O¤£¸Ó¦³ªº¿ù»~¡A§Ú¥ç¸ÕµÛ
±N E ©ú½T«Å§i¬° Variant¡A ¥u­nµo¥Í²§ª¬¯«¥P¤]µL¸Ñ¡A
¥u¯àµ¥¥¦®ð®ø¤F¡I

P. S. ¶¶«K½Ð±Ð¡A³Ìªñºô­¶¬O¤£¬O³Q§ó§ï¨ì¤F¡A
         §Úªº¥k¤W¨¤­Ó¤H¸ê°T¥uÅã¥Ü
[module]ÃäÄæ¼Ò¶ô_§Úªº§U¤â[/module]

c_c_lai

¦^´_ 10# GBKEE
³o¤@©Û¤]¦­´N¸Õ¹L¤F¡AµL®Ä¡I
§ÚÆ[¹î¹L¬O D.KEYS ±a­ÈÂà¤J®Éªº¦s­È°ÝÃD¡C
¥ç§Yµo¥Í¦b For Each E In D.KEYS ªº¤§«e¡C
¥Ø«e«ç»ò´ú¤]³£§ä¤£¥X¡AÆZÆF²§ªº¡C

c_c_lai

¦^´_ 16# jackyliu
³o¬O¸g¹L§Ú´ú¸Õ¹L OK ªº¡AÁöµM¤º®e¤j­P¤@¼Ë¡A
¦ýÁÙ¬O¨Ï¥Î§Úªºµ{¦¡½X¸Õ¸Õ¬Ý¡C
(¤§«e§Ú¥ç´ú¥X§A©Ò»¡ªºª¬ªp¡A¸Õ¸Õ³o°¦¬Ý¬Ý¡A¨ä¤¤¤]±N Debug ªº¹Lµ{¤º®e¥ç¨Ö§@¦¨µùÄÀ)

1. Option Explicit
   
2. 
   
3. Sub Ex()
   
4.     Dim Dk As Object, E As Variant, cts As Integer
   
5.     '  Dim Dk As Object, E, cts As Integer
   
6.    
   
7.     Set Dk = CreateObject("Scripting.dictionary")         '  ¦r¨åª«¥ó
   
8.    
   
9.     '  1. ±N Sheet1 ªº¸ê®Æ,½Æ»s¨ì Sheet2 ªº A1 ¦ì¸m¶}©l,¨Ì§Ç¼g¤J.
   
10.     '  2. ­«½Æ©Êªº¸ê®Æ,¤£­n¦A­«½Æ½Æ»s¨ìSheet2
    
11.     '  3. ¤ñ¸û¤£¥i­«½ÆÄæ¦ì:©m¦W,¦a°Ï,©Ê§O,±B«Ã
    
12.     For Each E In Sheet1.Range("A1").CurrentRegion.Rows  '  ª«¥ó: A1 ©Ò©µ¦ù½d³òªº¦C
    
13.         '  E.Value : Variant/Variant(1 to 1, 1 to 6) : ThisWorkbook.Ex
    
14.         '  E.Value(1,1) : "©m¦W" : Variant/String : ThisWorkbook.Ex2
    
15.         '  E.Value(1,2) : "¦a°Ï" : Variant/String : ThisWorkbook.Ex2
    
16.         '  E.Value(1,3) : "©Ê§O" : Variant/String : ThisWorkbook.Ex2
    
17.         '  E.Value(1,4) : "±Ð¨|µ{«×" : Variant/String : ThisWorkbook.Ex2
    
18.         '  E.Value(1,5) : "±B«Ã" : Variant/String : ThisWorkbook.Ex2
    
19.         '  E.Value(1,6) : "¤l¤k" : Variant/String : ThisWorkbook.Ex2
    
20.         Dk(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
    
21.     Next
    
22.    
    
23.     With Sheet2
    
24.         .Cells.Clear
    
25.         cts = 1
    
26.         For Each E In Dk.KEYS
    
27.             '  UBound(Dk(E), 1) : 1 : Long : ThisWorkbook.Ex
    
28.             '  UBound(Dk(E), 2) : 6 : Long : ThisWorkbook.Ex
    
29.             '  E : "©m¦W¦a°Ï©Ê§O±B«Ã" : Variant/String : ThisWorkbook.Ex2
    
30.             '  E : "¤p§õ¥x¥_¤k¤w±B" : Variant/String : ThisWorkbook.Ex2
    
31.             '  E : "¤p¼B®ç¶é¨k¤w±B" : Variant/String : ThisWorkbook.Ex2
    
32.             .Cells(cts, "A").Resize(1, UBound(Dk(E), 2)).Value = Dk(E)   '  Åª¨ú¦r¨åª«¥óªº ITEM (°}¦C)
    
33.         cts = cts + 1
    
34.         Next
    
35.     End With
    
36. End Sub

½Æ»s¥N½X

½Ð§â¥H¤W½Æ»sªºµ{¦¡½X©ñ¤J¨ì ThisWorkbook µ{¦¡½X°Ï¤º°õ¦æ¡C
P.S.  ¥Ø«e¦b§ÚªºÀɮפ¤ Module1 °Ï¥ç©ñ¤J¬Û¦Pµ{¦¡½X¤À§O´ú¸Õµ²ªG (Sub ¦WºÙ¤£¦P)¡A
        ³o¬O¬°¤F¤è«K´ú¸Õ "«¬ºA¤£²Å" °ÝÃD©Ò¦b¤§¬G¡C

c_c_lai

¦^´_ 16# jackyliu
¦^´_ 17# GBKEE
°ÝÃD¥i¯à¥X¦b

1. D(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value

½Æ»s¥N½X

Hsieh ª©¤jÀ³¥Î¤F

1. d(mystr) = Application.Transpose(Application.Transpose(a.Resize(, 6).Value))

½Æ»s¥N½X

¦b°}¦C­È²¾Âत³z¹L   Application.Transpose()¡A¨Ï±o¼Æ­È±o¥H¥¿½T Assign¡A¨äí©w«×¤ñª½±µ
Assign ·|¨Ó±o½T¹ê¡B¸ê®Æ Assigment ¤¤¸û¤£©ö¬y¥¢¡C§Ú±N Hsieh ª©¤jªºµ{¦¡¥[¤WÅܼƫŧi¡A
¸ü©ó¦p¤U¡G

1. Sub ex()                                              '  Hsieh
   
2.     Dim d As Object, a As Range, mystr As String
   
3.    
   
4.     Set d = CreateObject("Scripting.Dictionary")
   
5. 
   
6.     With Sheet1
   
7.         For Each a In .Range(.[A1], .[A1].End(xlDown))
   
8.             mystr = a & a.Offset(, 1) & a.Offset(, 2) & a.Offset(, 4)
   
9.             '  If d.exists(mystr) Then MsgBox a & "¸ê®Æ­«½Æ"
   
10.             d(mystr) = Application.Transpose(Application.Transpose(a.Resize(, 6).Value))
    
11.         Next
    
12.     End With
    
13.    
    
14.     With Sheet2
    
15.         .Cells.ClearContents
    
16.         .[A1].Resize(d.Count, 6) = Application.Transpose(Application.Transpose(d.items))
    
17.     End With
    
18. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 20# GBKEE
±z¥Ø«e¬Ý¨ìªº E ©ÎªÌ¬O E As Variant ³£¤w¦h¤è´ú¸Õ¹L¡A
·í®É E ¬°¦ó­×§ï¦¨ ©ú½T«Å§i¥ç¬O¦¹¥Ñ¨Ó¡C
¥Ø«e¥uµ¥«Ý jackyliu ªº´ú¸Õµ²ªG¤F¡A
§Ú·Q·¥¦³¥i¯à¯uªº¬O Assign ªº°ÝÃD¡A
¦]¬°°ÝÃDµo¥Í®É§Ú¦³Ãö¬d¨ì

1.         For Each E In Dk.KEYS
   
2.             '  E ¶Ç¤J­È¬° "µL"¡A ¥H­Pµo¥Í¥H¤U©µ¥Íªº "«¬ºA¤£²Å" ¿ù»~°T®§
   
3.             .Cells(cts, "A").Resize(1, UBound(Dk(E), 2)).Value = Dk(E)   '  Åª¨ú¦r¨åª«¥óªº ITEM (°}¦C)

½Æ»s¥N½X

c_c_lai

¦^´_ 20# GBKEE
²×©ó§ä¨ì°ÝÃD¼xµ²ÂI¤F¡C ·í°õ¦æ§¹«á

1. For Each E In Sheet1.Range("A1").CurrentRegion.Rows
   
2.     D(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
   
3. Next

½Æ»s¥N½X

D.KEYS °}¦C­È¤º®eÀ³¸Ó¦@¦³¤»­Ó¡A§Y¡G

1. '  D.KEYS :  : Variant/Variant(0 to 5) : ThisWorkbook.ex
   
2. '  D.KEYS(0) : "©m¦W¦a°Ï©Ê§O±B«Ã" : Variant/String : ThisWorkbook.ex
   
3. '  D.KEYS(1) : "¤p§õ¥x¥_¤k¤w±B" : Variant/String : ThisWorkbook.ex
   
4. '  D.KEYS(2) : "¤p¼B®ç¶é¨k¤w±B" : Variant/String : ThisWorkbook.ex
   
5. '  D.KEYS(3) : "¤p³¯·s¦Ë¤k¥¼±B" : Variant/String : ThisWorkbook.ex
   
6. '  D.KEYS(4) : "¤p±i¤¤Ãc¤k¥¼±B" : Variant/String : ThisWorkbook.ex
   
7. '  D.KEYS(5) : "¤p´ö¥x¤¤¨k¤w±B " : Variant/String : ThisWorkbook.ex

½Æ»s¥N½X

¦¹®É¡A«h°õ¦æ¤@¤Á¶¶§Q¡C ¤Ï¤§¡A·í³B²z°}¦Cµ²ªG¬°¤U¦C±¡ªp®É¡G

1. '  D.KEYS :  : Variant/Variant(0 to 8) : ThisWorkbook.ex
   
2. '  D.KEYS(0) : "©m¦W¦a°Ï©Ê§O±B«Ã" : Variant/String : ThisWorkbook.ex
   
3. '  D.KEYS(1) : "¤p§õ¥x¥_¤k¤w±B" : Variant/String : ThisWorkbook.ex
   
4. '  D.KEYS(2) : "¤p¼B®ç¶é¨k¤w±B" : Variant/String : ThisWorkbook.ex
   
5. '  D.KEYS(3) : 0 : Variant/Integer : ThisWorkbook.ex
   
6. '  D.KEYS(4) : 1 : Variant/Integer : ThisWorkbook.ex
   
7. '  D.KEYS(5) : 2 : Variant/Integer : ThisWorkbook.ex
   
8. '  D.KEYS(6) : "¤p³¯·s¦Ë¤k¥¼±B" : Variant/String : ThisWorkbook.ex
   
9. '  D.KEYS(7) : "¤p±i¤¤Ãc¤k¥¼±B" : Variant/String : ThisWorkbook.ex
   
10. '  D.KEYS(8) : "¤p´ö¥x¤¤¨k¤w±B " : Variant/String : ThisWorkbook.ex

½Æ»s¥N½X

¤W«Ò¡B¦ò¯ª°Ú¡I
·í³ò°é°õ¦æ¨ì D.KEYS(3)¡BD.KEYS(4)¡BD.KEYS(5) ´N¤¤¼Ö³z¤F¡C
³oªí¥Ü¦b

1. For Each E In Sheet1.Range("A1").CurrentRegion.Rows
   
2.     D(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
   
3. Next

½Æ»s¥N½X

³B¸Ì¶¥¬q¥X¤F°ÝÃD¡C À³¸Ó±N­«½Æ­È¤©¥H¹LÂo½Õ¡C

c_c_lai

¦^´_ 20# GBKEE
¦^´_ 23# jackyliu

1. Option Explicit
   
2. 
   
3. Sub ex()
   
4.     Dim D As Object, E As Variant, cts As Integer
   
5.    
   
6.     Set D = CreateObject("Scripting.dictionary")         '  ¦r¨åª«¥ó
   
7.    
   
8.     For Each E In Sheet1.Range("A1").CurrentRegion.Rows  '  ª«¥ó: A1 ©Ò©µ¦ù½d³òªº¦C
   
9.         If D.exists(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = False Then _
   
10.                D(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
    
11.     Next
    
12.    
    
13.     With Sheet2
    
14.         .Cells.Clear
    
15.         cts = 1
    
16.         For Each E In D.KEYS
    
17.             .Cells(cts, "A").Resize(1, UBound(D(E), 2)).Value = D(E)   '  Åª¨ú¦r¨åª«¥óªº ITEM (°}¦C)
    
18.         cts = cts + 1
    
19.         Next
    
20.     End With
    
21. 
    
22.     Set D = Nothing
    
23. End Sub

½Æ»s¥N½X

¥[¤J¤F¹LÂo§PÂ_¡AIf D.exists(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = False Then ....¡A
jackyliu ½Ð¦A¸Õ¬Ý¬Ý¡I

c_c_lai

¦^´_ 28# GBKEE
¦^´_ 26# jackyliu
¸ÕµÛ±N¥H¤Uµ{¦¡½X­×

1. For Each E In D.KEYS
   
2.     .Cells(cts, "A").Resize(1, UBound(D(E), 2)).Value = D(E)   '  Åª¨ú¦r¨åª«¥óªº ITEM (°}¦C)
   
3.     cts = cts + 1
   
4. Next

½Æ»s¥N½X

§ï¦¨

1. For Each E In D.KEYS
   
2.     If E <> "" Then _
   
3.         .Cells(cts, "A").Resize(1, UBound(D(E), 2)).Value = D(E)   '  Åª¨ú¦r¨åª«¥óªº ITEM (°}¦C)
   
4.     cts = cts + 1
   
5. Next

½Æ»s¥N½X

¦A±N¥¦¾Þ½m¤@¤U¡C

P.S. ¦^ÂФH®a®É¡A½Ð¨Ï¥Î¿ï«ö "¦^ÂÐ" ¶s¡A§_«h·í¨Æ¤H¬O¤£ª¾¹D©p¤w¦^ÂаÝÃD¤F¡I
       (³o¤]¬O¤@ºØ¶Ç²Î§¸`)

c_c_lai

¦^´_ 30# jackyliu
´ú¸Õ¬Ý¬Ý¬O§_¯à¥¿±`°õ¦æ¡H
ÁÂÁ©p¡I