ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¸ê®Æ³B²z-§ä¥X°ß¤@ªº¸ê®Æ

¦^´_ 2# Hsieh

Hsieh ¤j¤j~ «Ü·PÁ§Aªº¦^ÂÐ~
³o´N¬O§Ú·Q­n¤F¸Ñ¤Î·Q­n§ó²`¤J¾Ç²ß¨ÓÀ³¥Îªº»yªk~
¥i¥H²q±o¥X¾ã­Ó¼¶¼gªºÅÞ¿è,¦ý¤£²M·¡¤@¨Ç»yªkªº¨Ï¥Î¤è¦¡~
½Ð¤j¤j¯à¸ÑÄÀ¤@¤U~ À°¤p§Ì¸Ñ´b¤@¤U
  1. Sub Ex()

  2. Set d = CreateObject("Scripting.Dictionary")
  3. '°ÝÃDCreateObject:«Ø¥ß¨Ã¶Ç¦^¹ï©ó ActiveX ª«¥óªº¤Þ¥Î¡C
  4. '["Scripting.Dictionary"]<==³o¬O¤°»ò·N«ä©O??
  5. '¦]¬°¤j¤j¦³¨Ç®É­Ô¥Îªº¤è¦¡¡A«öF1¬O¬d¤£¨ìªº

  6. ar = Range("A1").CurrentRegion.Value
  7. 'CurrentRegion:¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C
  8. '¤p§Ì²qar¬O§_¬°¥ÑA1¶}©l¨ìªÅ¥ÕÃä¬ÉªºÀx¦s®æ½d³ò

  9. For i = 1 To UBound(ar, 1)
  10. 'UBound:ªí¥Ü«ü©w°}¦C¬Yºû³Ì¤j¥i¨Ï¥Îªº°}¦C¯Á¤Þ¡C
  11. '¥ÎUBound¨Ó§ì¨úar½d³ò¤@ºûªº³Ì¤j°}¦C¯Á¤Þ¡C

  12.   If d(ar(i, 1)) = "" Then
  13. 'd ¬O²Ä¤@¥ySet d = CreateObject("Scripting.Dictionary")¤¤ªºd¡A¦ý¤£¤F¸Ñ«ç»ò¸ÑŪ¡C
  14.      d(ar(i, 1)) = ar(i, 2)
  15.      ElseIf InStr(d(ar(i, 1)), ar(i, 2)) = 0 Then
  16. 'InStr¬O¥Î¨Ó¤ñ¹ïd(ar(i, 1)), ar(i, 2)¬O§_¦³¤@¼Ëªº¸ê®Æ¡A­Y¨S¦³¤@¼Ë«hshow0,0=0´N¬Oªí¥Üar(i, 2)¬O­n·s¼Wªº
  17.      d(ar(i, 1)) = d(ar(i, 1)) & "/" & ar(i, 2)
  18.   End If
  19. Next
  20. [J:K] = ""
  21. [J1].Resize(d.Count, 1) = Application.Transpose(d.keys)
  22. [K1].Resize(d.Count, 1) = Application.Transpose(d.items)
  23. 'Application.Transpose(d.keys)»PApplication.Transpose(d.items) ´N¤@ÂIÀYºü¤]¨S¦³¡A¤£ª¾¹D«ç»ò¨Ï¥Î??
  24. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 1# hugh0620
  1. Sub Ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. ar = Range("A1").CurrentRegion.Value
  4. For i = 1 To UBound(ar, 1)
  5.   If d(ar(i, 1)) = "" Then
  6.      d(ar(i, 1)) = ar(i, 2)
  7.      ElseIf InStr(d(ar(i, 1)), ar(i, 2)) = 0 Then
  8.      d(ar(i, 1)) = d(ar(i, 1)) & "/" & ar(i, 2)
  9.   End If
  10. Next
  11. [J:K] = ""
  12. [J1].Resize(d.Count, 1) = Application.Transpose(d.keys)
  13. [K1].Resize(d.Count, 1) = Application.Transpose(d.items)
  14. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD