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

[µo°Ý] °õ¦æ¿ù»~

[µo°Ý] °õ¦æ¿ù»~

½Ð°Ý¦U¦ì¤j¤j¡A

§Ú¼g¤F¤@¬q¥¨¶°¡A
¶]¨ì²Ä14¦æ    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("SNAME").Sheets("Á`ªí").Range("a" & i)
·|¥X²{°õ¦æ¶¥¬q¿ù»~¡A°}¦C¯Á¤Þ¶W¥X½d³ò
§Ú¤£¾å±o¬O­þ¸Ì¥X¿ù¡A
Áٳ·Фj®a«üÂI¡AÁÂÁ¡C
  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)

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

­×§ï¦p¤U¬õ¦r¡AÁÂÁÂ
©Ò¦³Workbooks("SNAME") §ï¬° Workbooks(SNAME)


Sub §¹¦¨_Á`µ²new()

i = 2
If Range("a2") = "" Then
    r = 2
Else
    r = Range("a1").End(xlDown).Row + 1
End If

FN = Sheets("³]©w­¶").Range("B5")
Workbooks.Open Filename:=FN
SNAME = ActiveWorkbook.Name
Do While Range("a" & i) <> ""
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks(SNAME).Sheets("Á`ªí").Range("a" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks(SNAME).Sheets("Á`ªí").Range("e" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks(SNAME).Sheets("Á`ªí").Range("f" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks(SNAME).Sheets("Á`ªí").Range("j" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks(SNAME).Sheets("Á`ªí").Range("i" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks(SNAME).Sheets("Á`ªí").Range("d" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks(SNAME).Sheets("Á`ªí").Range("l" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks(SNAME).Sheets("Á`ªí").Range("m" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks(SNAME).Sheets("Á`ªí").Range("q" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks(SNAME).Sheets("Á`ªí").Range("t" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks(SNAME).Sheets("Á`ªí").Range("ab" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks(SNAME).Sheets("Á`ªí").Range("y" & i)
    Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks(SNAME).Sheets("Á`ªí").Range("z" & i)
'    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
        If Workbooks(SNAME).Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks(SNAME).Sheets("Á`ªí").Range("q" & i) <> "" Then
            Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
        Else
            Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks(SNAME).Sheets("Á`ªí").Range("aa" & i)

        End If
    r = r + 1
    i = i + 1
Loop

Workbooks(SNAME).Close
'x = WorksheetFunction.CountIf(Sheets("³sµ²Á`ªí").Range("c:d"), Range("m:m"))
End Sub

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2022-1-25 03:34 ½s¿è

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


­É§Ú§ï¤@§ï

2.¦~«×°V½m­pµeªí.xlsm.zip (36.73 KB)

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD