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

[µo°Ý] ¨Ï¥ÎÀx¦s®æ³]©w¨Ó«ü©w¬¡­¶

[µo°Ý] ¨Ï¥ÎÀx¦s®æ³]©w¨Ó«ü©w¬¡­¶

¦U¦ì¤j¤j¦n

§Ú¦³¨â­ÓÀɮסA
¤@­Ó¬O"1.¦~«×°V½mÁ`ªí-109"¡A«á­±ªº109·|ÀH¦~«×¤£¦P¦Ó§ïÅÜ
¥t¤@­Ó"2.¦~«×°V½m­pµeªí"
"2.¦~«×°V½m­pµeªí"·|¥h§ì"1.¦~«×°V½mÁ`ªí-109"ªº¸ê®Æ¨Ó°µ¾ã²z¡A

¥Ø«e§Ú¦³­«·s­×§ï¤@¤U¥¨¶°"§¹¦¨_Á`µ²new()"
¦b"2.¦~«×°V½m­pµeªí"¦³°µ¤@­Ó³]©w­¶¡AÅܧó¦~«×·|¶}±Ò¨º­Ó¦~«×ªº¸ê®Æ
¦ý­n°õ¦æDo Whileªº®É­Ô§ÚÁÙ¬O­n¤@­Ó­Ó­×§ï¦¨§Ú­nªº¦~«×
Á|¨Ò¤§«e¬O"1.¦~«×°V½mÁ`ªí-108"§Ú´N­n¶i¥h§ï¦¨"1.¦~«×°V½mÁ`ªí-109"
³o­Ó¬O¤£¬O¤]¯à°Ñ·Ó³]©w­¶ªº¸ê°T©O¡H

¥t¥~·Q½Ð°Ý¦³¿ìªk¤£¥´¶}¬¡­¶Ã¯´N¯à§ì¨ú¸ê®Æ¥X¨Ó¶Ü¡H

¥¨¶°¼g±o¦³ÂI¶ÃÁٽШ£½Ì
  1. Sub §¹¦¨_Á`µ²new()

  2. i = 2
  3. If Range("a2") = "" Then
  4.     r = 2
  5. Else
  6.     r = Range("a1").End(xlDown).Row + 1
  7. End If

  8. Workbooks.Open Filename:=Sheets("³]©w­¶").Range("B5")

  9. Do While Range("a" & i) <> ""
  10.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("a" & i)
  11.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("e" & i)
  12.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("f" & i)
  13.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("j" & i)
  14.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("i" & i)
  15.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("d" & i)
  16.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("l" & i)
  17.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("m" & i)
  18.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("q" & i)
  19.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("t" & i)
  20.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("ab" & i)
  21.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("y" & i)
  22.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("z" & i)
  23. '    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("aa" & i)
  24.         If Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("q" & i) <> "" Then
  25.             Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
  26.         Else
  27.             Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("aa" & i)

  28.         End If
  29.     r = r + 1
  30.     i = i + 1
  31. Loop

  32. Workbooks("1.¦~«×°V½mÁ`ªí-109").Close

  33. End Sub
½Æ»s¥N½X

±Ð¨|°V½m.zip (76.76 KB)

¥ý¶}1.¦~«×°V½mÁ`ªí-109.xlsm
¦A¶}2.¦~«×°V½m­pµeªí.xlsm
°õ¦æ

±Ð¨|°V½m.zip (70.95 KB)

TOP

¦^´_ 10# samwang


±z¦n¡AÁÂÁ±z
¦@¦P§@·~ªº¤H­û§Æ±æ¥ý§ï¬°¥H¤Uªº¤è¦¡¾Þ§@¡A
¦]¤Ó²`ªº¥¨¶°¥LµLªk¤F¸Ñ¥B¤§«á¥LµLªk°µ­×§ï¡A
¬G§Ú­Ì°Q½×«á¥u¦n±N­ì¥»¥¨¶°°µ¤F²³æ½Õ¾ã¤è«K¥L¤é«á°µºûÅ@¡A
¦ý¥X²{°õ¦æ¿ù»~¡A°}¦C¯Á¤Þ¶W¥X½d³ò
¦]§Ú­Ì§ä¤£¥X¿ù»~­ì¦]¬°¦ó¡A
¬G·Q½Ð±Ð¤j¤jÀ°¦£

±z¤§«e©Ò´£¨Ñªº¥¨¶°¡A§Ú·|¯d¤U§@­Ó¤H¨Ï¥Î¡AÁÂÁÂ
  1. Sub §¹¦¨_Á`µ²new()

  2. i = 2
  3. If Range("a2") = "" Then
  4.     r = 2
  5. Else
  6.     r = Range("a1").End(xlDown).Row + 1
  7. End If
  8. MsgBox (r)

  9. SNAME = Sheets("³]©w­¶").Range("B5")
  10. Workbooks.Open Filename:=SNAME

  11. Do While Range("a" & i) <> ""
  12.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("SNAME").Sheets("Á`ªí").Range("a" & i)
  13.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks("SNAME").Sheets("Á`ªí").Range("e" & i)
  14.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks("SNAME").Sheets("Á`ªí").Range("f" & i)
  15.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i)
  16.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks("SNAME").Sheets("Á`ªí").Range("i" & i)
  17.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks("SNAME").Sheets("Á`ªí").Range("d" & i)
  18.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks("SNAME").Sheets("Á`ªí").Range("l" & i)
  19.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks("SNAME").Sheets("Á`ªí").Range("m" & i)
  20.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i)
  21.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks("SNAME").Sheets("Á`ªí").Range("t" & i)
  22.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks("SNAME").Sheets("Á`ªí").Range("ab" & i)
  23.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks("SNAME").Sheets("Á`ªí").Range("y" & i)
  24.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks("SNAME").Sheets("Á`ªí").Range("z" & i)
  25. '    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
  26.         If Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i) <> "" Then
  27.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
  28.         Else
  29.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)

  30.         End If
  31.     r = r + 1
  32.     i = i + 1
  33. Loop

  34. Workbooks(SNAME).Close
  35. 'x = WorksheetFunction.CountIf(Sheets("³sµ²Á`ªí").Range("c:d"), Range("m:m"))
  36. End Sub
½Æ»s¥N½X

±Ð¨|°V½m.zip (70.35 KB)

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2022-1-4 11:28 ½s¿è
½Ð°Ý¬O­þ¸Ì¥X¿ù¤F¶Ü¡H¤@ª½¥X²{°õ¦æ¿ù»~
ÅÚ½³ªd µoªí©ó 2021-12-30 17:09


§Ú¥Î¤§«e´£¨Ñ6#µ{¦¡½X°õ¦æµ²ªGµL»~¦p¹Ï¤ù¡A³£¦³²Å¦X±zªº»Ý¨D¡A
¿ïÀÉ®×-->µM«á½Æ»s¸ê®Æ-->¶K¨ì"³sµ²Á`ªí"
½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

22.JPG (274.09 KB)

22.JPG

TOP

½Ð°Ý¬O­þ¸Ì¥X¿ù¤F¶Ü¡H¤@ª½¥X²{°õ¦æ¿ù»~
  1. Sub §¹¦¨_Á`µ²new()

  2. i = 2
  3. If Range("a2") = "" Then
  4.     r = 2
  5. Else
  6.     r = Range("a1").End(xlDown).Row + 1
  7. End If

  8. SNAME = Sheets("³]©w­¶").Range("B5")
  9. Workbooks.Open Filename:=SNAME

  10. Do While Range("a" & i) <> ""
  11.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("SNAME").Sheets("Á`ªí").Range("a" & i)
  12.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks("SNAME").Sheets("Á`ªí").Range("e" & i)
  13.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks("SNAME").Sheets("Á`ªí").Range("f" & i)
  14.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i)
  15.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks("SNAME").Sheets("Á`ªí").Range("i" & i)
  16.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks("SNAME").Sheets("Á`ªí").Range("d" & i)
  17.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks("SNAME").Sheets("Á`ªí").Range("l" & i)
  18.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks("SNAME").Sheets("Á`ªí").Range("m" & i)
  19.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i)
  20.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks("SNAME").Sheets("Á`ªí").Range("t" & i)
  21.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks("SNAME").Sheets("Á`ªí").Range("ab" & i)
  22.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks("SNAME").Sheets("Á`ªí").Range("y" & i)
  23.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks("SNAME").Sheets("Á`ªí").Range("z" & i)
  24. '    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
  25.         If Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i) <> "" Then
  26.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
  27.         Else
  28.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)

  29.         End If
  30.     r = r + 1
  31.     i = i + 1
  32. Loop

  33. Workbooks(SNAME).Close
  34. 'x = WorksheetFunction.CountIf(Sheets("³sµ²Á`ªí").Range("c:d"), Range("m:m"))
  35. End Sub
½Æ»s¥N½X

±Ð¨|°V½m.zip (70.35 KB)

TOP

¦^´_  samwang
¦nªº¡AÁÂÁ¡A³Ìªñ¤ñ¸û¦£§Ú·|¦A§ä®É¶¡´ú¸Õ
¥t¥~·Q½Ð°ÝWorkbooks("1.¦~«×°V½mÁ`ªí-10 ...
ÅÚ½³ªd µoªí©ó 2021-12-14 14:56


¥t¥~·Q½Ð°ÝWorkbooks("1.¦~«×°V½mÁ`ªí-109")-->>³o­Ó¯àÀH³]©w­¶ÅܰʶܡH
>> ¤£¤Ó¯à¤F¸Ñ±zªº·N«ä¡A¥iÁ|¨Ò¤@¨Ç½d¨Ò¶Ü? ÁÂÁÂ

TOP

¦^´_ 6# samwang


¦nªº¡AÁÂÁ¡A³Ìªñ¤ñ¸û¦£§Ú·|¦A§ä®É¶¡´ú¸Õ
¥t¥~·Q½Ð°ÝWorkbooks("1.¦~«×°V½mÁ`ªí-109")-->>³o­Ó¯àÀH³]©w­¶ÅܰʶܡH

TOP

¦^´_  samwang
±z¦n¡A¦]¬°§Úªº¸ê®Æ§¨¸Ì­±¤£¥u¤@­ÓexcelÀÉ¡A¥i¥H¥Î«ü©wªº¤è¦¡¶Ü¡H
  Workbooks("1. ...
ÅÚ½³ªd µoªí©ó 2021-12-13 09:29


§ï¥Î¿ï¾ÜÀɮפ覡¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim FileOrg$, fc%, wb, a, Dr, Sr, j%
Application.ScreenUpdating = False: Application.DisplayAlerts = False
FileOrg = ActiveWorkbook.Name
Sheets("³sµ²Á`ªí").[a1].CurrentRegion.Offset(1) = ""
With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "D:\"
    .Title = "===========¿ï¾Ü  ""¦~«×°V½mÁ`ªí""  ÀÉ®×==========="
    .AllowMultiSelect = False: .Show
    fc = .SelectedItems.Count: If fc = 0 Then Exit Sub
    a = .SelectedItems(1)
    Set wb = Workbooks.Open(a)
    With Sheets("Á`ªí")
        If .FilterMode Then .ShowAllData
        Set xA = .[a1]
        R = xA(Rows.Count, 1).End(xlUp).Row
        Dr = Array(1, 5, 6, 10, 9, 4, 12, 13, 17, 20, 28, 25, 26, 27)   '¸ê®Æ¨Ó·½
        Sr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)       '¶¶§Ç
        For j = 0 To UBound(Dr)
            Workbooks(FileOrg).Sheets("³sµ²Á`ªí").Cells(2, Sr(j)).Resize(R).Value = xA(2, Dr(j)).Resize(R).Value
        Next
    End With
End With
wb.Close
Set wb = Nothing:  Set xA = Nothing
Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub

TOP

¦^´_ 4# samwang


  ±z¦n¡A¦]¬°§Úªº¸ê®Æ§¨¸Ì­±¤£¥u¤@­ÓexcelÀÉ¡A¥i¥H¥Î«ü©wªº¤è¦¡¶Ü¡H
  Workbooks("1.¦~«×°V½mÁ`ªí-109")-->>³o­Ó¯àÀH³]©w­¶ÅܰʶܡH

TOP

¦^´_ 3# ÅÚ½³ªd

¬O©ñ¦b¦P¤@­Ó¦~«×ªº¸ê®Æ§¨¸Ì¡A
§Ú¼g¤£¥X¨Óªº¬O Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("a" & i)
­n¦p¦óÅýWorkbooks("1.¦~«×°V½mÁ`ªí-109")¸òµÛ³]©w­¶ÅÜ°Ê
>> 2­ÓÀɮשñ¦b¦P¤@­Ó¸ê®Æ§¨¡A¥u­n¶}±Ò"2.¦~«×°V½m­pµeªí"´N·|¦Û°Ê§ì¨ú"1.¦~«×°V½mÁ`ªí-109"¸ê®Æ¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

·s¼W¸ê®Æ§¨.zip (130.74 KB)

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD