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

[µo°Ý] ½Ð°Ý¦p¦ó±NAÀÉ®×(³sÄò©Ê)ªº¸ê®Æ¡A¸g¹L¿z¿ï«á¼g¤JBÀɮתº«ü©wÄæ¦ì¤¤©O¡H

[µo°Ý] ½Ð°Ý¦p¦ó±NAÀÉ®×(³sÄò©Ê)ªº¸ê®Æ¡A¸g¹L¿z¿ï«á¼g¤JBÀɮתº«ü©wÄæ¦ì¤¤©O¡H

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2015-7-14 21:26): ¸ê®Æ¾ã²z»P­ì©l¸ê®Æµ²ºcÃö«Y±K¤Á¡A«Øij¤W¶Ç­ì©l¸ê®Æ¤Î¾ã²z«áªº»Ý¨DÀɮסA¥H«K¤F¸Ñ

¥Ø«e¦³¼Æ­Ó³sÄò½s¸¹ªºÀɮס]¨Ò¦p¡Gtest_1¡@¨ì¡@10¡^¡A
¸Ì­±ªºÄæ¦ì±ø¥ó³£¤@¼Ë¡A¨Ò¦pA=©m¦W¡BB=¤é´Á¡BC=®É¶¡¡BD=¤À¼Æ¡BE=¤j¤p.......................¡A¦ý¬O¦³¨Ç¦³­«½Æ©ÎªÅÄæ¦ì¡A
§Ú·Q±N³o¨ÇÀɮצX¨Ö¼g¨ì¤@­ÓÀɮ׸̡]¨Ò¦p¡GDATA¡^¡A¦ý¬O¥u¬D¿ï¬Y¨ÇÄæ¦ì¼g¤J¡A¼g¤J«áÄæ¦ìÅܦ¨A=©m¦W¡BB=
¤À¼Æ¡BC=¤j¤p..................¡A¥B­ì©lÀɮפ¤­Y¦³­«½Æ¤£¤À¤£­n¼g¤J¡A
½Ð°ÝVBA«ç»ò¨Ó¼¶¼g¡H

¥»©«³Ì«á¥Ñ kuhsuanchieh ©ó 2015-7-14 23:28 ½s¿è

¦^´_ 1# Hsieh
·PÁª©¥DHsieh´£¿ô¡A»¡©ú¦p¤U







´ú¸Õ¸ê®Æ§¨.rar (28.69 KB)

TOP

¦^´_ 2# kuhsuanchieh
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D As Object, AR As Variant, i As Long, XPath As String, Wb As String
  4.     Set D = CreateObject("SCRIPTING.DICTIONARY")     '¦r¨åª«¥ó
  5.     '±N³o¨ÇÀɮצX¨Ö¼g¨ì¤@­ÓÀɮ׸̡]¨Ò¦p¡GDATA¡^¡A
  6.     '¥Ø«e¦³¼Æ­Ó³sÄò½s¸¹ªºÀɮס]¨Ò¦p¡Gtest_1¡@¨ì¡@10¡^¡A
  7.     XPath = "d:\test\ " '³]¦³¼Æ­Ó³sÄò½s¸¹ªºÀɮצs©ó¦P¤@¸ê®Æ§¨
  8.     Wb = Dir(XPath & "Test_*.xls")    '¬d¸ß«ü©w©Ò»ÝªºÀÉ®×
  9.     Application.ScreenUpdating = False
  10.     Do While Wb <> ""                  '¬d¸ß¨ì©Ò»ÝªºÀÉ®×
  11.         With Workbooks.Open(XPath & Wb).Sheets(1).UsedRange
  12.             '¶}±ÒÀɮײĤ@­Ó¤u§@ªíªº¨Ï¥Î½d³ò
  13.             For i = 2 To .Rows.Count    '²Ä2¦C¨ì³Ì«á¤@¦C
  14.                 With .Rows(i)
  15.                     AR = Array(.Cells(1, "c"), .Cells(1, "L"), .Cells(1, "D"), .Cells(1, "M"), .Cells(1, "F"))
  16.                     'Ū¨ú C,L,D,M,F Äæ¦ì¸ê®Æ
  17.                     D(Join(AR, ",")) = ""  '¼g¤J¦r¨åª«¥óªºKey­È
  18.                 End With
  19.             Next
  20.             .Parent.Parent.Close      'Ãö³¬¶}±ÒªºÀÉ®×
  21.             '.UsedRangeªº[Parent]-> .Sheets(1)ªº[Parent] -> Workbooks
  22.         End With
  23.         Wb = Dir   '¤U¤@­Ó ¬d¸ßªºÀÉ®×
  24.     Loop
  25.    
  26.     '¦p[DATA.xlsx] ¥¼¶}±Ò¥Î¤U¦¡µ{¦¡½X
  27.     'With Workbooks.Open(XPath & "\" & DATA.xlsx).Sheets(1)
  28.    
  29.     With Workbooks("DATA.xlsx").Sheets(1)
  30.         For i = 2 To .UsedRange.Rows.Count
  31.             AR = Application.Transpose(Application.Transpose(.UsedRange.Rows(i).Value))
  32.             If D.exists(Join(AR, ",")) Then D.Remove Join(AR, ",")
  33.             '¦r¨åª«¥ó Remove ¤èªk,±q¤@­Ó Dictionary ª«¥ó¤¤²¾°£¤@­ÓÃöÁä¦r©M¶µ¥Ø¹ï¡C
  34.         Next
  35.         For Each AR In D.keys   '²¾°£¬Û¦PKey(ÃöÁä¦r)«á³Ñ¤UªºD.keys
  36.             i = .[A1].End(xlDown).Row
  37.             i = IIf(i = .Rows.Count, 2, i + 1) 'i=³Ì«á¤@¦Cªº¦C¼Æ,i=2,§_i+1
  38.             With .Cells(i, "A").Resize(, 5)
  39.                 .Value = Split(AR, ",")
  40.                 .Cells(5).NumberFormatLocal = "[>99999999]0000-000-000;000-000-000"
  41.                 .Value = .Value   '¼Æ¦r¬°¤å¦r®æ¦¡Âର¼Æ¦r®æ¦¡
  42.             End With
  43.        Next
  44.        .Save   '¦sÀÉ
  45.     End With
  46.     Application.ScreenUpdating = True
  47. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE


·PÁ±zªºÀ°¦£¡A¤w¸g¸Ñ¨M¤F¡I

ÁÂÁ¡I

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD