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

[µo°Ý] ·j´M²Å¦X±ø¥óªº­q³æ¸¹½X

¦^´_ 7# Hsieh

¤j¤j,

§Ú·Q±N§A¼gªº³o­Óµ{¦¡®M¨ì¥H¤U³o´X­Ó¼Ò¦¡¤¤¡A¥Ø«e¥u¥ý¹Á¸Õ"­q³æ©ú²Óªí",¦ý¤@ª½¤£¦¨¥\¡I¥i§_½Ð§A¥Ü½d¤U¦p¦ó­×§ïµ{¦¡¤º®e¡H    ¥X³f¤å¥ó³sµ².rar (97.66 KB)

¨Ó·½ÀÉ Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\FromERP\
­q³æ©ú²Óªí.xlsx
½ÐÁʳæ©ú²Óªí.xlsx
±ÄÁʳæ©ú²Óªí.xlsx
¶i³f³æ©ú²Óªí.xlsx
»â®Æ³æ©ú²Óªí.xlsx
¥X³f³æ©ú²Óªí.xlsx

¥ØªºÀÉ Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\ERP_Data.xlsx
­q³æ.sheet
½ÐÁÊ.sheet
±ÄÁÊ.sheet
¶i³f.sheet
»â®Æ.sheet
¥X³f.sheet

·Ç«h¦s©ñªºÀɮפÎÀx¦s®æ
Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\VBA³øªí«ü¥O.xlsm
­q³æ©ú²Óªí            H2
½ÐÁʳæ©ú²Óªí        H3
±ÄÁʳæ©ú²Óªí        H4
¶i³f³æ©ú²Óªí        H5
»â®Æ³æ©ú²Óªí        H6
¥X³f³æ©ú²Óªí        H7

­q³æ©ú²Óªíªº®Mªí¼Ò¦¡¬°¡G
1. ¥HVBA³øªí«ü¥O.xlsm H2¬°·Ç«h,·j´M­q³æ©ú²ÓªíªºB2Äæ¦ì,·í²Å¦XH2®É(¥Ø«eªº­È¬OMSO17060001)
2. ¥Ø«e²Å¦XªºÀx¦s®æ¬OB11,«hcopy B11~BAªº¸ê®Æ³Ì©³ºÝ,¦pªG§ä¤£¨ì¥Nªí¨S¦³¸ê®Æ¥i½Æ»s.
3. ¶K¤W¸ê®Æ¦Ü¥ØªºÀÉ Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\ERP_Data.xlsxªº"­q³æ.sheet",²Å¦XBÄæMSO17060001ªº¦ì¸m,¨Ãª½±µÂл\­ì¸ê®Æ,¿ï¾Ü©Ê¶K¤W­È(¤£­n§ó§ï­ì¸ê®Æªº®æ¦¡),¦pªG§ä¤£¨ì®É,´N·í¦¨¬O³Ì·sªº¸ê®Æ,ª½±µ±q¥ØªºÀɪºBÄæ³Ì©³ºÝ¤U¤@Ä檺ªÅ¥Õ¦C¶K¤W(©Ò¥H­n¯à¦Û°Ê°»´ú¸ê®Æªº³Ì¥½ºÝ,¦pªG¦³¥þ¦CªÅ¥Õ(«D¥þ¦CªÅ¥Õ¤£ºâ¬OªÅ¥Õ),¨äªÅ¥Õªº²Ä¤@¦C(­Y¦³ªÅ¥Õ¦C«á¦A¥X²{ªº¸ê®Æµø¦PªÅ¥Õ)§Y¬O¶K·s¸ê®Æªº¦a¤è.

ª`·N¨Æ¶µ:
1. ¨Ó·½ÀɤΥتºÀɪº¸ê®Æ³£¨Ó¦Û¨t²ÎDownloadªº³øªí,©Ò¥H¸ê®Æ¦h¹è¬O·|Åܰʪº
2. ·Ç«h¦s©ñªºH2~H7,¨ä­È¤]·|ÀHµÛ»Ý¨D¦ÓÅÜ°Ê
3. ¥ØªºÀɨC­Ósheet³£¦³³]©w²Õ¦¨¸s²Õ,¬O§_À³¦³¥ý¥´¶}¸s²Õªº°Ê§@,¸ê®Æ¤~·|¶K¥¿½T¦ì¸m¡H
4. ·í¸ê®Æ¶K§¹¥H«á,­n¦AÁôÂøs²Õ

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-7-27 07:45 ½s¿è

¦^´_ 11# PJChen
­«·s¦^¨ì 11# ªº°ÝÃD
°ÝÃD½ÆÂø¥i¥H¤@­Ó¤@­Ó¨Ó
**********************************
­q³æ©ú²Óªíªº®Mªí¼Ò¦¡¬°¡G
1. ¥HVBA³øªí«ü¥O.xlsm H2¬°·Ç«h,·j´M­q³æ©ú²ÓªíªºB2Äæ¦ì,·í²Å¦XH2®É(¥Ø«eªº­È¬OMSO17060001)
2. ¥Ø«e²Å¦XªºÀx¦s®æ¬OB11,«hcopy B11~BAªº¸ê®Æ³Ì©³ºÝ,¦pªG§ä¤£¨ì¥Nªí¨S¦³¸ê®Æ¥i½Æ»s.
3. ¶K¤W¸ê®Æ¦Ü¥ØªºÀÉ Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\ERP_Data.xlsxªº"­q³æ.sheet",²Å¦XBÄæMSO17060001ªº¦ì¸m,¨Ãª½±µÂл\­ì¸ê®Æ,¿ï¾Ü©Ê¶K¤W­È(¤£­n§ó§ï­ì¸ê®Æªº®æ¦¡),¦pªG§ä¤£¨ì®É,´N·í¦¨¬O³Ì·sªº¸ê®Æ,ª½±µ±q¥ØªºÀɪºBÄæ³Ì©³ºÝ¤U¤@Ä檺ªÅ¥Õ¦C¶K¤W(©Ò¥H­n¯à¦Û°Ê°»´ú¸ê®Æªº³Ì¥½ºÝ,¦pªG¦³¥þ¦CªÅ¥Õ(«D¥þ¦CªÅ¥Õ¤£ºâ¬OªÅ¥Õ),¨äªÅ¥Õªº²Ä¤@¦C(­Y¦³ªÅ¥Õ¦C«á¦A¥X²{ªº¸ê®Æµø¦PªÅ¥Õ)§Y¬O¶K·s¸ê®Æªº¦a¤è.
********************************
  1. Option Explicit
  2. Dim ¥ØªºÀÉ As Workbook, ¨Ó·½ÀÉ As Workbook
  3. Dim ·Ç«h³æ¸¹ As String, ·Ç«h³æ¸¹_Rng As Range
  4. Dim Äæ¦ì As String, ¤u§@­¶ As String, Msg As String
  5. Sub Main()
  6.     Dim Table As Range, Sh As Worksheet, i As Integer
  7.     Msg = ""
  8.     With ThisWorkbook.Sheets("VBA«ü¥O")           '³]©w·Ç«h½d³ò
  9.         Set Table = .Range("G2", .Range("G2").End(xlDown)).Resize(, 2)   '
  10.     End With
  11.     File_settings ¥ØªºÀÉ, "ERP_Data.XLSX"    '³]©w¥ØªºÀÉ
  12.     For i = 1 To Table.Rows.Count
  13.         File_settings ¨Ó·½ÀÉ, Table.Cells(i, 1) & ".XLSX"  '³]©w¨Ó·½ÀÉ
  14.         ·Ç«h³æ¸¹ = Table.Cells(i, 2)          'Ū¨ú·Ç«h
  15.         ¤u§@­¶ = Mid(Table.Cells(i, 1), 1, 2)    '¥ØªºÀɪº¤u§@ªí¦WºÙ
  16.         Äæ¦ì = IIf(¤u§@­¶ = "­q³æ" Or ¤u§@­¶ = "¶i³f" Or ¤u§@­¶ = "»â®Æ", "C:C", "B:B") '¥ØªºÀɪº¤u§@ªíªºÄæ¦ì
  17.         xSearch
  18.         ¨Ó·½ÀÉ.Close False
  19.     Next
  20.     '******************************
  21.     '¥ØªºÀÉ.Close True   ¼È®É¤£¦sÀÉ
  22.     '******************************
  23.     If Msg <> "" Then MsgBox Msg
  24. End Sub
  25. Private Sub xSearch()
  26.     Dim D As Object, M, ¤u§@­¶ As String
  27.     Set D = CreateObject("SCRIPTING.DICTIONARY") '¦r¨åª«¥ó
  28.     With ¨Ó·½ÀÉ.Sheets(1) '.Range("b:b")          '¨Ó·½ÀɲĤ@­Ó¤u§@ªíªºBÄæ
  29.         .Cells.Sort .Range("B1"), 1, Header:=xlYes   '±Æ§Ç
  30.         M = Application.Match(·Ç«h³æ¸¹, .Range("b:b").Cells, 0)
  31.         '**********************************
  32.         If IsError(M) Then Exit Sub  '¨Ó·½ÀɨS¦³§ä¨ì·Ç«h,Â÷¶}³oµ{¦¡ (¤£³B²z)
  33.         '**§ä¨ì·Ç«h,³]©w·Ç«hªº¸ê®Æ½d³ò
  34.         With .Range("b:b")
  35.             Do While .Cells(M) = ·Ç«h³æ¸¹
  36.                 '.Range("B" & M & ":BA" & M) -> ¦@27Äæ
  37.                 If TypeName(D(.Cells(M).Value)) <> "Range" Then
  38.                     Set D(·Ç«h³æ¸¹) = .Range("a" & M).Resize(, 27)
  39.                 Else
  40.                     Set D(·Ç«h³æ¸¹) = Union(D(·Ç«h³æ¸¹), .Range("a" & M).Resize(, 27))
  41.                 End If
  42.                 M = M + 1
  43.             Loop
  44.         End With
  45.         Set ·Ç«h³æ¸¹_Rng = D(·Ç«h³æ¸¹)
  46.     End With
  47.     ¥ØªºÀÉ_·Ç«h³æ¸¹
  48. End Sub
  49. Private Sub ¥ØªºÀÉ_·Ç«h³æ¸¹()
  50.     Dim M As Variant, D As Object, xRng As Range, i As Integer, ¤u§@­¶³æ¸¹_Rng As Range
  51.    
  52.     With ¥ØªºÀÉ.Sheets(¤u§@­¶)
  53.         '¥ØªºÀɪº¤u§@­¶¦³µL·Ç«h³æ¸¹
  54.         M = Application.Match(·Ç«h³æ¸¹, .Range(Äæ¦ì), 0)
  55.         '**************************
  56.         If IsError(M) Then   'µL·Ç«h³æ¸¹
  57.             M = Split(.UsedRange.Address, "$")
  58.             M = M(UBound(M))       '¤u§@­¶³Ì©³ºÝªº¦C
  59.             Do While Application.CountA(.Rows(M)) > 1  '¥²»Ý¨S¦³¸ê®Æ
  60.                 M = M + 1
  61.             Loop
  62.             '*********************************
  63.             Set ¤u§@­¶³æ¸¹_Rng = .Range(Äæ¦ì).Cells(M).Resize(·Ç«h³æ¸¹_Rng.Rows.Count, ·Ç«h³æ¸¹_Rng.Columns.Count)
  64.             Msg = Msg & vbLf & ¤u§@­¶ & " ¥[¤J: " & ·Ç«h³æ¸¹
  65.         Else       ''¦³·Ç«h³æ¸¹
  66.             Set D = CreateObject("SCRIPTING.DICTIONARY")
  67.             .Cells.Sort .Range(Äæ¦ì).Cells(1), 1, Header:=xlYes            '¥ý±Æ§Ç
  68.             With .Range(Äæ¦ì)
  69.                 M = Application.Match(·Ç«h³æ¸¹, .Cells, 0)  '´M§ä³æ¸¹¦C¸¹
  70.                 '³]©w ¤u§@­¶·Ç«h³æ¸¹ªº½d³ò*********
  71.                 Do While .Cells(M) = ·Ç«h³æ¸¹
  72.                     If TypeName(D(.Cells(M).Value)) <> "Range" Then
  73.                         Set D(·Ç«h³æ¸¹) = .Range("a" & M).Resize(, 27)
  74.                     Else
  75.                         Set D(·Ç«h³æ¸¹) = Union(D(·Ç«h³æ¸¹), .Range("a" & M).Resize(, 27))
  76.                     End If
  77.                     M = M + 1
  78.                 Loop
  79.             End With
  80.             With D(·Ç«h³æ¸¹)
  81.                 If .Rows.Count > ·Ç«h³æ¸¹_Rng.Rows.Count Then  '¤u§@­¶³æ¸¹¦C¼Æ>·Ç«h³æ¸¹¦C¼Æ
  82.                     For i = .Rows.Count To ·Ç«h³æ¸¹_Rng.Rows.Count + 1 Step -1
  83.                         Rows(i).EntireRow.Delete                    '¾ã¦C§R°£
  84.                     Next
  85.                 ElseIf .Rows.Count < ·Ç«h³æ¸¹_Rng.Rows.Count Then   '¤u§@­¶³æ¸¹¦C¼Æ<·Ç«h³æ¸¹¦C¼Æ
  86.                     For i = .Rows.Count + 1 To ·Ç«h³æ¸¹_Rng.Rows.Count
  87.                         Rows(i + 1).EntireRow.Insert                '·s¼W¤@¦C
  88.                     Next
  89.                 End If
  90.             End With
  91.             Set ¤u§@­¶³æ¸¹_Rng = D(·Ç«h³æ¸¹).Resize(D(·Ç«h³æ¸¹).Rows.Count)
  92.             Msg = Msg & vbLf & ¤u§@­¶ & " §ó·s: " & ·Ç«h³æ¸¹ & " §¹²¦"
  93.         End If
  94.     End With
  95.     With ¤u§@­¶³æ¸¹_Rng
  96.         .Value = ·Ç«h³æ¸¹_Rng.Value
  97.         .BorderAround ColorIndex:=3, Weight:=xlThick
  98.     End With
  99. End Sub
  100. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  101.     Dim xPath As String
  102.     xPath = ThisWorkbook.Path & "\"
  103.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  104.     On Error Resume Next
  105.     Set xFile = Workbooks(¤u§@­¶)
  106.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  107.     If xFile.Name = "" Then
  108.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  109.         End
  110.     End If
  111. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 12# GBKEE


¤j¤j,
§Ú¬D¤F¨ä¤¤¤@­ÓÀɨӻ¡©ú...
§Ú¬ÝµÛ¥¦°õ¦æ´X¦¸¡Aªì®É¦³ÂI¬Ý¤£À´¡A¦]¬°¨Ó·½ÀɤΥتºÀɪº¸ê®Æ³£¹ï¤£¤W¡A¦Ó¥B¥¦°õ¦æ¹Lµ{¤¤¤@ª½¦b¦sÀÉ¡C«á¨Ó§Úª½±µ§â¨Ó·½ÀɤΥتºÀɪº³æ¾Ú¸¹½X¶K¦b¤@°_¤ñ¹ï¡Aµo²{¦p¤U¡G
1) ¥ØªºÀɪº¸ê®Æ³Q±Æ§Ç¹L¡A¦Ó¥B¥u¦³³æ¸¹±Æ§Ç¡A¨ä¥LªºÄæ¨S¦³¸òµÛÅÜ°Ê¡A©Ò¥H¸ê®Æ¿ù¶Ã(¨ä¹ê§Ú¤£­n¸ê®Æ±Æ§Ç¡A§Ú¥u­n«ö­ì¸ê®Æ¶K¤W¦Ó¥B¬O¶K¤W­È´N¦n)
2) ¥ØªºÀɪº¸ê®Æ¸ò¤§«e¤@¼Ë,·í¨Ó·½Àɸê®Æ¤ñ¸û¤Ö®É,¥¦µLªkDelete¦h¾lªº¸ê®Æ (§Ú¤@ª½¦b«ä¦Ò³o­Ó°ÝÃD,¦pªG³o­Ó³¡¥÷ªºµ{¦¡«ÜÃø¹F¦¨¡A°®¯Ü©Ò¦³ªºÀɮצb§ó·s¸ê®Æ«á¡A©Ò¦³¶K¤Wªº¸ê®Æ³£ºû«ù¤Ï¶Âªºª¬ºA¡A·N«ä´N¹³¤U­±ªº¹ÏÀÉ¡A§ó·sªº¸ê®Æ¬OB:BF15747,Åý¤Ï¶Â«O¯d¤U¨Ó,§Ú¤@¬Ý´Nª¾¹D­þ­Ó°Ï¶ô¬O§ó·s¹Lªº¡A§Ú¦A¤â°ÊDelete 15748:15755¡A§Aı±o³o¼Ë¦p¦ó¡H·|¤£·|¤ñ¸û¦n¼g¡H


3) °õ¦æ¹Lµ{¤¤¤@ª½¦b¦sÀÉ¡A³o­Ó³¡¥÷§Ú·Q¥u­n³Ì«á¦sÀÉ´N¦n¤F¡A¤£µMÀɮפj¤Ï¦Óªá®É¶¡¤@ª½¦bµ¥«Ý¡A¦Ó¥B§Ú·QERP_Data.xlsx §Ú¤@ª½³£¦b¨Ï¥Î¡A§ï¬°¦sÀɫᤣÃö³¬.
4) ...¨ä¹ê§Ú¤]·w¤F¡A¤£ª¾¹D«ç»ò¦^ÂФñ¸û¦n¡Aµ¥§ï¦n¥H¤W³o¨Ç§Ú¦Atry¬Ý¬Ý¦³¤°»ò°ÝÃD§a.
5) ÁÙ¦³Y...§Ú¤§«e¤G­Ó¦³ÃöÁpªºµo°Ý©ñ¦b¦P¤@­Ó¥DÃD¤¤³£³Q§A§R°£¤F¡A§Ú¤S­n­«·sµo°Ý¤F¡A§Ú¤w¸g¥ý±N¨ä¤¤¤@­Ó¸û²³æªº¥ýPO¤W¥h¡A¥t¤@­Ó¥¿¦n§Ú¦b§@·~·í¤¤¡Aµo²{»Ý¨D¦³ÅÜ°Ê¡Aµ¥§Ú·Q¦n¤F½T©w­n«ç»ò°õ¦æ¦APO¤W§a¡A·íµM§Ú·|¥Î¥t¤@­Ó¥DÃD¡A¤£·|²V¦b¤@°_Åý§Aµo©ü¤F¡C

TOP

¦^´_ 13# PJChen

¤j¤j,

§Ú¤µ¤Ñ¤@´ú¸Õ´N¥d¦í¤F¡A§Ú¤£ª¾¹D¦³¤°»ò¦a¤è­n­×§ï¡A©Ò¥H¥ýPO¤W¨Óµ¹§A¬Ý...
ÁöµM§R¤F«Ü¦h¸ê®Æ,ÀÉ®×ÁÙ¬O¶W¹L1MB,©Ò¥H§Ú¦³¤À³Î¦A¤W¶Ç.

Try_20170726.part1.rar (600 KB)
Try_20170726.part2.rar (463.64 KB)

TOP

¦^´_ 14# PJChen

12#ªºµ{¦¡½X,¤µ¤Ñ¤w§ó·s,½Ð­«¸Õ¬Ý¬Ý,·Ç«h½Æ»s¨ì¥ØªºÀɦ³¥Î¬õ®Ø®Ø¤W.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 15# GBKEE

¤j¤j,
§Ú¿å¤F·sªºµ{¦¡¦n´X¦¸,¸£¤l¦³ÂI¿ù¶Ã¤F¡A§Ú¤£ª¾¹D©Ò°õ¦æªºµ²ªG¬O§_¯u¦p§Ú©Ò»¡¡A§Ú§â¨Ó·½Àɪº³æ¸¹³£¶K¦b¨C­Ó¥ØªºÀɪº³Ì«á¤@Äæ¥H¤è«K¨Ó¦^¤ñ¹ï¡A§Ú§â§Ú©Ò­nªº¤Î¬Ý¨ìªºµ²ªG´y­z¤@¤U...
»Ý¨D¡G
1. ³o6­Ósheet¯uªº¨S¦³±Æ§Çªº»Ý¨D,½Ð¤£­nÅý¥¦±Æ§Ç,¤£µM¸ê®Æ·|¶Ã±¼.
2. §ó·s¤§«á,¤]¤£­n¦b¥ØªºÀÉ¥[¬õ¦â®Ø½u,¦]¬°¥¦¦b¥\¯à¤W¨S¦³ªº¹ê½èªº»Ý¨D,¤£¹L¦]¬°§A³o¦¸¥[¤F®Ø½u,§Úµo²{·Ç«h¤£­nÅÜ°Ê¡A°õ¦æ¤F2¦¸¥H«á´N¦³2­Ó®Ø½u,³o¬O§_»¡©ú¥¦ªº°õ¦æµ²ªG¦³»~¡H

¬Ý¨ìªºµ²ªG¡G
3. ¥¦¦n¹³§ó·s¸ê®Æ¨ì²ÄN¦C´N¤£¯à§ó·s,«á­±¦³¨Ç·sªº¸ê®Æ§¹¥þ¨S¦³¶K¤W.
4. ¦h¾lªº¸ê®ÆµLªk§R°£,§Ú¤£½T©w·sªºµ{¦¡¬O§_¨S¦³³o­Ó¥\¯à¡H
5. §Ú¦b¥X³f.sheet¹êÅç,§R°£¤F«Ü¦h¸ê®Æ,·Q¬Ý¥¦ªº§ó·s±¡§Î¡Aµo²{¨S¦³§ó·s¥\¯à¡I

µ²½×¡G
6. ©Ò¥H¨ä¹ê§Ú¤£ª¾¹D¥¦¨ì©³¯à§_§ó·s¸ê®Æ.

Try_20170727.part1.rar (600 KB) Try_20170727.part2.rar (466.57 KB)

TOP

¦^´_ 16# PJChen

1.¨S¦³±Æ§Çªº»Ý¨D
  ¨Ó·½ÀÉ,¥ØªºÀɤ¤ªº³æ¸¹,³£¤£¤@©w¥u¦³¤@¦C¹ï¶Ü?
  ¨º¨Ó·½ÀÉ,¥ØªºÀɤ¤ªº³æ¸¹³£·|³sÄò±Æ¦b¤@°_¶Ü?
³o¦³Ãöµ{¦¡½Xªº½s¼g
2¤£­n¦b¥ØªºÀÉ¥[¬õ¦â®Ø½u,¦]¬°¥¦¦b¥\¯à¤W¨S¦³ªº¹ê½èªº»Ý¨D
   ¥[¬õ®Ø¬OÄ[§A¬Ý¨ì§ó·sªº¸ê®Æ¦b¨º¸Ì,
  1. With ¤u§@­¶³æ¸¹_Rng
  2.         .Value = ·Ç«h³æ¸¹_Rng.Value
  3.        '³o¥i¥H§R±¼¥¦
  4.        '.BorderAround ColorIndex:=3, Weight:=xlThick
  5.     End With
½Æ»s¥N½X
3. ¥¦¦n¹³§ó·s¸ê®Æ¨ì²ÄN¦C´N¤£¯à§ó·s,«á­±¦³¨Ç·sªº¸ê®Æ§¹¥þ¨S¦³¶K¤W
.   §Aªº½d¨Ò      ­q³æ©ú²Óªí-> MSO17020036 ¦bB2   ,B2¥H«áªº¸ê®Æ¬O·sªº¸ê®Æ¶Ü?
   ¥þ³£¶K¨ì ERP_Datqªº­q³æ¶Ü!

4. ¦h¾lªº¸ê®ÆµLªk§R°£,§Ú¤£½T©w·sªºµ{¦¡¬O§_¨S¦³³o­Ó¥\¯à¡H
  ÁÙ¨S,½Ð°Ý¦p¦ó§PÂ_¦h¾lªº¸ê®Æ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2017-7-29 19:56 ½s¿è

¦^´_ 17# GBKEE

¤j¤j,

«e­±³\¦h¦¸ªº¹êÅç¡A·Pı¬O¦]¬°µ{¦¡»Ý¨D¤£°÷©ú½T¡A©Ò¥H°õ¦æ®É¤@ª½³£¦³¿ù»~¡A³o¦¸§Ú§â¥ØªºÀɪº¶K¤WÄæ¦ì§ï¬°¤@¼Ë,§Ú·Q³o¼Ë¤ñ¸û¦³¤@­P©Ê¡A¦Ó¥B§Ú¥i¯à­n¥Î¨ì³o­ÓÄæ¦ì¡A¥i¥HÁקK¤é«áªº­×§ï¡A¥i§_§â«e­±ªººØºØ©Ù±¼¡A­«·s¨Ó¹L¡H

·Ç«h¡G       
1..        ·Ç«h¨Ó¦ÛVBA³øªí«ü¥O.xlsm VBA«ü¥O.sheet G2:H7
2..       


       
¨Ó·½ÀɳQ½Æ»s³W«h¡G       
3..        ·Ç«h«ü©wªº³æ¸¹¬O¨Ó·½ÀÉ­n³Q½Æ»s¤º®eªº°_©lÂI(·í¦P¤@³æ¸¹¦³¦hµ§®É,¥H²Ä¤@µ§¬°°_©l·Ç«h),¤@ª½¨ì³Ì«á¤@¦C¬°µ²§ô
4..        ·Ç«h«ü©wªº³æ¸¹­Y¦b¨Ó·½Àɧ䤣¨ì¡K.´N¬O¨S¦³¤º®e­n³Q½Æ»s(ª½±µ¸õ¹L)
       
¥ØªºÀɧó·s³W«h¡G       
5..        ·Ç«h«ü©wªº³æ¸¹¤]¬O¥ØªºÀÉ­n§ó·s¤º®eªº°_©lÂI(·í¦P¤@³æ¸¹¦³¦hµ§®É,¥H²Ä¤@µ§¬°°_©l·Ç«h),¤@ª½¨ì³Ì«á¤@¦C¬°µ²§ô
        ·í§ó·s¸ê®Æ¶K¤W«á,³Ì«á¤@¦C­YÁÙ¦³¸ê®Æ,«hclear¤@¾ã¦C(¥ØªºÀÉsheet©Ò«ü©w§ó·s¤º®eªº°Ï¶¡,Column«e«á¬Ò¦³¸ê®Æ,©Ò¥H­nclear¤@¾ã¦C)


6..        ·Ç«h«ü©wªº³æ¸¹­Y¦b¥ØªºÀɧ䤣¨ì¡K.ªí¥Ü§ó·sªº¸ê®Æ¬O¥þ·sªº,´N±q¥ØªºÀɪº³Ì«á¤@¦C¶K¤W§ó·s¸ê®Æ


VBAµ{¦¡»Ý¨D¡G       
7..        ¨Ó·½ÀɤΥتºÀɪº¤º®e¦h¹è¨Ã¨S¦³¤@©w¡A©Ò¥H¤£¯à¥Î«ü©wÀx¦s®æªº¤è¦¡°µ½Æ»s.¶K¤W
8..        ¥ØªºÀɭ쥻ªº®æ¦¡¤£­n³QÂл\,©Ò¥H§ó·s®É¥u­n¶K¤W­È
9..        ©Ò¦³sheetªº³æ¸¹¦P¤@­Ó¤ëªº¤º®e¨ÃµL¶¶§Ç,¥u¦³¤é´Á¤~¦³¶¶§Ç,©Ò¥H¨Ó·½ÀɤΥتºÀɪº¤º®e³£¤£­n³Q±Æ§Ç
10..        ¨Ó·½ÀɤΥتºÀɦbµ{¦¡°õ¦æ®É­n¥i¥H¦Û°Ê§PÂ_Àɮ׬O§_¦³¶}±Ò,¥¼¶}±Ò«h¨Ï¥Î®É¦Û°Ê¥´¶}
11..        §ó·s¸ê®Æ«á,¨Ó·½Àɤ£­n¦sÀɪ½±µÃö³¬
12..        §ó·s¸ê®Æ«á,¥ØªºÀɳQ§ó·sªº¤º®e«O¯d¤Ï¶Âªº°Ï¶ô (³o­Ó¥u¬O¬°¤F¤è«KÀ˵ø³Q§ó·sªº¤º®e,¦Ó¤S¤£·|¦³®Ø½u¦s¦b,¦pªG³o­Ó³¡¥÷ªºµ{¦¡¤ÓÃø¼g,¥i¥H©¿²¤)


13..        §ó·s¸ê®Æ«á,¥ØªºÀɳ̫á¤~¦sÀɦӥB¤£­nÃö³¬
Try_20170729.part1.rar (550 KB)
Try_20170729.part2.rar (502.08 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-7-30 07:53 ½s¿è

¦^´_ 18# PJChen
¦³°÷²M·¡ªº,´£°Ý´N¬O­n³o¼Ë
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Dim ¥ØªºÀÉ As Workbook, ¨Ó·½ÀÉ As Workbook
  3. Sub Copy_All()
  4.     Dim RngAr(), Rng As Range, M As Variant, xM As Long, E As Integer, Msg As String
  5.     With ThisWorkbook.Sheets("VBA«ü¥O")
  6.         RngAr = .Range("G2", .Range("G2").End(xlDown)).Resize(, 3).Value   '·Ç«h½d³ò¸m©ó°}¦CÅܼƤ¤
  7.     End With
  8.     File_settings ¥ØªºÀÉ, "ERP_Data.XLSX"
  9.     '****  ¤ñ¹ï·Ç«h ªº°j°é **************
  10.     For E = 1 To UBound(RngAr)
  11.         File_settings ¨Ó·½ÀÉ, RngAr(E, 1) & ""
  12.         M = Application.Match(RngAr(E, 2), ¨Ó·½ÀÉ.Sheets(1).Range("b:b"), 0)
  13.         If IsNumeric(M) Then
  14.             With ¨Ó·½ÀÉ.Sheets(1)
  15.                 xM = .Range("b" & M).End(xlDown).Row
  16.                Set Rng = .Range("b" & M, .Range("b" & xM)).Resize(, Range(RngAr(E, 3)).Columns.Count)
  17.                Rng.copy
  18.             End With
  19.             With ¥ØªºÀÉ.Sheets(Mid(RngAr(E, 1), 1, 2))
  20.                 M = Application.Match(RngAr(E, 2), .Range("c:c"), 0)
  21.                 If IsNumeric(M) Then
  22.                     '***¶K¤W«á½d³ò,·|¦Û°Êªº¤Ï¥Õ,¤£¶·¦³µ{¦¡½X°µ¤Ï¥Õ***
  23.                     .Range("c" & M).PasteSpecial xlPasteValues
  24.                     xM = .Range("c" & M).End(xlDown).Row  '¶K¤W«á³o¤u§@ªíªº³Ì«á¤@¦C¸¹
  25.                     M = M + Rng.Rows.Count - 1            '·Ç«h¦C¸¹+½Æ»s½d³òªº¦C¼Æ - 1
  26.                     If xM > M Then .Range("a" & M + 1, .Range("a" & xM)).EntireRow.Delete
  27.                 Else
  28.                     '***¶K¤W«á½d³ò,·|¦Û°Êªº¤Ï¥Õ,¤£¶·¦³µ{¦¡½X°µ¤Ï¥Õ***
  29.                     .Range("c1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
  30.                 End If
  31.             End With
  32.             Application.CutCopyMode = False
  33.             Msg = Msg & vbLf & Mid(RngAr(E, 1), 1, 2) & vbTab & RngAr(E, 2) & vbTab & "§ó·s§¹¦¨!"
  34.         Else
  35.             Msg = Msg & vbLf & Mid(RngAr(E, 1), 1, 2) & vbTab & RngAr(E, 2) & vbTab & "¤£¥Î§ó·s!"
  36.         End If
  37.         ¨Ó·½ÀÉ.Close False
  38.     Next
  39.     ¥ØªºÀÉ.Save     '**¥ØªºÀɦsÀÉ
  40.     MsgBox IIf(Msg = "", "¨S¦³¥ô¦ó «ü©w­q³æ §ó·s", Mid(Msg, 2))
  41. End Sub
  42. '**********¨Ó·½ÀɬO¦PVBA³øªí«ü¥Oªº¸ê®Æ§¨\FromERP\*********
  43. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  44.     Dim xPath As String
  45.     xPath = ThisWorkbook.Path & "\"
  46.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  47.     On Error Resume Next
  48.     Set xFile = Workbooks(¤u§@­¶)
  49.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  50.     If xFile.Name = "" Then
  51.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  52.         End
  53.     End If
  54. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 19# GBKEE

GBKEE¤j¯«...°õ¦æ§¹¥þ¨S°ÝÃD.  

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD