- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-1-7 18:38 ½s¿è
¦^´_ 1# Genie
¸Õ¸Õ¬Ý- Option Explicit
- Sub Ex()
- Dim D(1 To 2) As Object, AR(), Rng As Range, i As Integer, K As Variant
- Set D(1) = CreateObject("SCRIPTING.DICTIONARY") '¦r¨åª«¥ó
- Set D(2) = CreateObject("SCRIPTING.DICTIONARY")
- Set Rng = Sheets("ì©l¸ê®Æ").Range("a2") 'Àx¦s®æª«¥ó
- Do
- '1. ¨Ì·Ó A Äæ§@°Ï¤À¡A±N¸ê®Æ¥Ñª½¦V±Æ¦CÅܬ°¾î¦V±Æ¦C¡C
- '2. Y¨Ì A Äæ§@°Ï¤À¡A´N¥H A Ä檺ȧ@¬°¼ÐÃD¡C
- If D(1).exists(Rng.Value) Then '¦r¨åª«¥ó.exists(Rng.Value) ÃöÁä¦r[¦s¦b] ±ø¥ó¦¨¥ß
- AR = D(1)(Rng.Value) '°}¦C=¦r¨åª«¥ó(ÃöÁä¦r)ªº¤º®e
- ReDim Preserve AR(UBound(D(1)(Rng.Value)) + 1) '°}¦CÂX¥R¼W¥[¤@¤¸¯À
- AR(UBound(AR)) = Rng.Cells(1, 3).Value '°}¦C¼W¥[ªº¤¸¯À=CÄ檺¼ÆÈ
- D(1)(Rng.Value) = AR '¦r¨åª«¥ó(ÃöÁä¦r)ªº¤º®e=°}¦C
- Else
- D(1)(Rng.Value) = Array(Rng.Cells(1, 3).Value) '¦r¨åª«¥ó(ÃöÁä¦r)ªº¤º®e=°}¦C
- End If
- '*********************************************
- '1. ¨Ì·Ó B Äæ§@°Ï¤À¡A±N¸ê®Æ¥Ñª½¦V±Æ¦CÅܬ°¾î¦V±Æ¦C¡C
- '2. Y¨Ì B Äæ§@°Ï¤À¡A´N¥H A-B Ä檺ȧ@¬°¼ÐÃD
- K = "'" & Rng & " - " & Rng.Cells(1, 2)
- If D(2).exists(K) Then
- AR = D(2)(K)
- ReDim Preserve AR(UBound(D(2)(K)) + 1)
- AR(UBound(AR)) = Rng.Cells(1, 3).Value
- D(2)(K) = AR
- Else
- D(2)(K) = Array(Rng.Cells(, 3).Value)
- End If
- Set Rng = Rng.Offset(1)
- Loop Until Rng = ""
- With Sheets("sheet1")
- .Cells.Clear
- If D(1).Count > 0 Then
- i = 1
- For Each K In D(1).keys 'K= ¦r¨åª«¥ó(ÃöÁä¦r)
- .Cells(1, i) = K
- .Cells(2, i).Resize(UBound(D(1)(K)) + 1) = Application.WorksheetFunction.Transpose(D(1)(K)) 'Ū¨ú¤º®e
- i = i + 1
- Next
- End If
- If D(2).Count > 0 Then
- i = 10
- For Each K In D(2).keys
- .Cells(1, i) = K
- .Cells(2, i).Resize(UBound(D(2)(K)) + 1) = Application.WorksheetFunction.Transpose(D(2)(K))
- i = i + 1
- Next
- End If
- End With
- End Sub
½Æ»s¥N½X |
|