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

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

¦^´_ 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

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

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

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD