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

ÅÚ½³ªd

¦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. 
   
3. i = 2
   
4. If Range("a2") = "" Then
   
5.     r = 2
   
6. Else
   
7.     r = Range("a1").End(xlDown).Row + 1
   
8. End If
   
9. 
   
10. Workbooks.Open Filename:=Sheets("³]©w­¶").Range("B5")
    
11. 
    
12. Do While Range("a" & i) <> ""
    
13.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("a" & i)
    
14.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("e" & i)
    
15.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("f" & i)
    
16.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("j" & i)
    
17.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("i" & i)
    
18.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("d" & i)
    
19.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("l" & i)
    
20.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("m" & i)
    
21.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("q" & i)
    
22.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("t" & i)
    
23.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("ab" & i)
    
24.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("y" & i)
    
25.     Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("z" & i)
    
26. '    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("aa" & i)
    
27.         If Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("q" & i) <> "" Then
    
28.             Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
    
29.         Else
    
30.             Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("1.¦~«×°V½mÁ`ªí-109").Sheets("Á`ªí").Range("aa" & i)
    
31. 
    
32.         End If
    
33.     r = r + 1
    
34.     i = i + 1
    
35. Loop
    
36. 
    
37. Workbooks("1.¦~«×°V½mÁ`ªí-109").Close
    
38. 
    
39. End Sub

½Æ»s¥N½X

ÅÚ½³ªd

¦^´_ 2# samwang


³o­Ó¬O¤£¬O¤]¯à°Ñ·Ó³]©w­¶ªº¸ê°T©O¡H
>> ³o¬O¤°»ò·N«ä? §Aªº³]©w­¶B1¤w¦³¦~«×¡A³o¤£¬O«Ü¦n¶Ü?>>¤£¦n·N«ä¡A§Ú¤£¤ÓÀ´§Aªº·N«ä
¥t¥~¤]¥i¥H±N2­ÓÀɮצP®É©ñ¦b¦P¤@­Ó¸ê®Æ§¨¡A³o¼Ë´N¤£¥Î³]©w¦~«×¡A¥i¥Hª½±µ¶}±Ò¥t¤@­ÓÀÉ®×
>>¬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­¶ÅÜ°Ê


¥t¥~·Q½Ð°Ý¦³¿ìªk¤£¥´¶}¬¡­¶Ã¯´N¯à§ì¨ú¸ê®Æ¥X¨Ó¶Ü¡H
>> ¥i¥H¶}±ÒÀɮ׫á¸ê®Æ¸Ë¤J°}¦C´NÃö³¬ÀɮסAµM«á³Ì«á°µ¸ê®Æ³B²z¡A³o¼ËÀ³¸Ó«Ü§Ö¤~¹ï¡C
>>¤£¦n·N«ä¡A½Ð§Ú¸Ó«ç¼g©O¡H

ÅÚ½³ªd

¦^´_ 4# samwang


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

ÅÚ½³ªd

¦^´_ 6# samwang


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

ÅÚ½³ªd

½Ð°Ý¬O­þ¸Ì¥X¿ù¤F¶Ü¡H¤@ª½¥X²{°õ¦æ¿ù»~

1. Sub §¹¦¨_Á`µ²new()
   
2. 
   
3. i = 2
   
4. If Range("a2") = "" Then
   
5.     r = 2
   
6. Else
   
7.     r = Range("a1").End(xlDown).Row + 1
   
8. End If
   
9. 
   
10. SNAME = Sheets("³]©w­¶").Range("B5")
    
11. Workbooks.Open Filename:=SNAME
    
12. 
    
13. Do While Range("a" & i) <> ""
    
14.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("SNAME").Sheets("Á`ªí").Range("a" & i)
    
15.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks("SNAME").Sheets("Á`ªí").Range("e" & i)
    
16.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks("SNAME").Sheets("Á`ªí").Range("f" & i)
    
17.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i)
    
18.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks("SNAME").Sheets("Á`ªí").Range("i" & i)
    
19.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks("SNAME").Sheets("Á`ªí").Range("d" & i)
    
20.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks("SNAME").Sheets("Á`ªí").Range("l" & i)
    
21.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks("SNAME").Sheets("Á`ªí").Range("m" & i)
    
22.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i)
    
23.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks("SNAME").Sheets("Á`ªí").Range("t" & i)
    
24.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks("SNAME").Sheets("Á`ªí").Range("ab" & i)
    
25.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks("SNAME").Sheets("Á`ªí").Range("y" & i)
    
26.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks("SNAME").Sheets("Á`ªí").Range("z" & i)
    
27. '    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
    
28.         If Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i) <> "" Then
    
29.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
    
30.         Else
    
31.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
    
32. 
    
33.         End If
    
34.     r = r + 1
    
35.     i = i + 1
    
36. Loop
    
37. 
    
38. Workbooks(SNAME).Close
    
39. 'x = WorksheetFunction.CountIf(Sheets("³sµ²Á`ªí").Range("c:d"), Range("m:m"))
    
40. End Sub

½Æ»s¥N½X

ÅÚ½³ªd

¦^´_ 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. 
   
3. i = 2
   
4. If Range("a2") = "" Then
   
5.     r = 2
   
6. Else
   
7.     r = Range("a1").End(xlDown).Row + 1
   
8. End If
   
9. MsgBox (r)
   
10. 
    
11. SNAME = Sheets("³]©w­¶").Range("B5")
    
12. Workbooks.Open Filename:=SNAME
    
13. 
    
14. Do While Range("a" & i) <> ""
    
15.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 1) = Workbooks("SNAME").Sheets("Á`ªí").Range("a" & i)
    
16.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 2) = Workbooks("SNAME").Sheets("Á`ªí").Range("e" & i)
    
17.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 3) = Workbooks("SNAME").Sheets("Á`ªí").Range("f" & i)
    
18.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 4) = Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i)
    
19.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 5) = Workbooks("SNAME").Sheets("Á`ªí").Range("i" & i)
    
20.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 6) = Workbooks("SNAME").Sheets("Á`ªí").Range("d" & i)
    
21.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 7) = Workbooks("SNAME").Sheets("Á`ªí").Range("l" & i)
    
22.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 8) = Workbooks("SNAME").Sheets("Á`ªí").Range("m" & i)
    
23.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 9) = Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i)
    
24.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 10) = Workbooks("SNAME").Sheets("Á`ªí").Range("t" & i)
    
25.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 11) = Workbooks("SNAME").Sheets("Á`ªí").Range("ab" & i)
    
26.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 12) = Workbooks("SNAME").Sheets("Á`ªí").Range("y" & i)
    
27.     Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 13) = Workbooks("SNAME").Sheets("Á`ªí").Range("z" & i)
    
28. '    Workbooks("2.¦~«×°V½m­pµeªí").Worksheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
    
29.         If Workbooks("SNAME").Sheets("Á`ªí").Range("j" & i) = "¥~°V" And Workbooks("SNAME").Sheets("Á`ªí").Range("q" & i) <> "" Then
    
30.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = "¸Ô¦p©ú²Ó"
    
31.         Else
    
32.             Workbooks("2.¦~«×°V½m­pµeªí").Sheets("³sµ²Á`ªí").Cells(r, 14) = Workbooks("SNAME").Sheets("Á`ªí").Range("aa" & i)
    
33. 
    
34.         End If
    
35.     r = r + 1
    
36.     i = i + 1
    
37. Loop
    
38. 
    
39. Workbooks(SNAME).Close
    
40. 'x = WorksheetFunction.CountIf(Sheets("³sµ²Á`ªí").Range("c:d"), Range("m:m"))
    
41. End Sub

½Æ»s¥N½X