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

[µo°Ý] ±µÄò¦s¸ê®Æªº°ÝÃD(¤w¸Ñ¨M)

[µo°Ý] ±µÄò¦s¸ê®Æªº°ÝÃD(¤w¸Ñ¨M)

¥»©«³Ì«á¥Ñ jesscc ©ó 2011-5-15 21:18 ½s¿è

§Ú¤S¦³°ÝÃD­n¥´ÂZ¦U¦ì¤F!
¦]¬°¾Þ§@¹Lµ{µy·L³Â·Ð¡A©Ò¥H¦bÀ£ÁYÀɤº¥tªþ»¡©ú
¦b¦¹¥ý²³æ»¡©ú¡A°ÝÃD©Ò¦b´N¬O:¦b¦P¤@Äæ¦ì±q¸ê®Æªº§ÀºÝ¡A±µÄò¶K·s¸ê®Æ¡C

Test.rar (49.05 KB)
Jess

¦^´_ 1# jesscc
  1. Sub Ex()
  2.     Dim Wo As Workbook, Sh As Worksheet, Rng As Range
  3.     For Each Wo In Workbooks
  4.         If Wo.Name <> "SPECforMRPtest.xls" Then
  5.             For Each Sh In Wo.Sheets
  6.                 With Workbooks("SPECforMRPtest.xls").Sheets("MRP").Cells(Rows.Count, "aw").End(xlUp).Offset(1)
  7.                     Set Rng = Sh.Range(Sh.[d7], Sh.[d7].End(xlDown))
  8.                     .Resize(Rng.Count, 1) = Rng.Value
  9.                     .Offset(, 1).Resize(Rng.Count, 1) = Rng.Offset(, 4).Value
  10.                 End With
  11.             Next
  12.         End If
  13.     Next
  14. End Sub
½Æ»s¥N½X

TOP

ÁÂÁ GBKEE ¤j¤j À°§Ú¸Ñ¨M°ÝÃD¡C
§Ú·d¤F¤@¤U¤È¡A­è­è¤]¤~§ä¨ì°ÝÃD©Ò¦b¤F¡A¥u­n§â Ay2() ùتº¨º­Ó "s" ¥þ³¡´«¦¨¥t¤@­Ó¥N¸¹´N¦n¤F¡C
Jess

TOP

¦^´_ 3# jesscc
¥u­n§â Ay2() ùتº¨º­Ó "s" ¥þ³¡´«¦¨¥t¤@­Ó¥N¸¹´N¦n¤F¡C  ³oÆ[©À¿ù»~
Dim Ay2()«Å§i¬°°ÊºA°}¦C
ReDim Preserve Ay2(s) Ä~©Ó«e­±ªº S ·|¦³26­ÓªÅ¥Õ¤¸¯À
Ay2(s) =Array(Cells(j, ar2(0)).Value, Cells(j, ar2(1)).Value)  ¬O¤@ºû°}¦C
©Ò¤@¸ê®Æ«¬ºA¤£²Å¦X
±NS³]¬°0 ¤§«á -> Ay2()¸Ìªº¤¸¯À¸ê®Æ«¬ºA¤~·|¤@­P
  1. '¨ú¥X Metal ¥X³f©ú²Ó
  2. aw.Activate
  3. Sheets("Metal").Activate
  4. s = 0  '    ½Ð¦bµ{¦¡½X¥[¤W³o¦æ     
  5. Dim Ay2()
  6. ar2 = Array("D", "H")
  7. For j = 7 To Cells(Rows.Count, 2).End(3).Row
  8.     ReDim Preserve Ay2(s)
  9.     Ay2(s) = Array(Cells(j, ar2(0)).Value, Cells(j, ar2(1)).Value)
  10.     s = s + 1
  11. Next
  12. Workbooks("SPECforMRPtest.xls").Activate
  13. With Sheets("MRP")
  14.    .Cells(.Rows.Count, 49).End(xlUp).Offset(1, 0).Resize(s, 2) = Application.Transpose(Application.Transpose(Ay2))
  15. End With   
  16. End Sub
½Æ»s¥N½X

TOP

GBKEE¤j¤j¦n:
"ReDim Preserve Ay2(s) Ä~©Ó«e­±ªº S ·|¦³26­ÓªÅ¥Õ¤¸¯À"

³o¥y¸Ü§Ú·d¤£¤ÓÀ´¬O¤°»ò·N«ä¡A§Ú¬O³o¼Ë²qªº:
¦]¬°Ay1()ªº S ¤w¸g¦s¤J¤F26µ§¸ê®Æ¡A©Ò¥HAy2()¦P¼Ë¥H S ¬°¥N¸¹¡A´N·|±q²Ä27¶}©l¦s¸ê®Æ¡A¦]¦¹Ay1()©MAy2()ªº¸ê®Æ§ÎºA´N·|¤£¦P¡A¬O³o­Ó·N«ä¶Ü?

´«¤@­Ó¥N¸¹©M´¡¤Js = 0 ³o¨âªÌ¤S¦³¤°»ò®t§O©O?·Pı¦n¹³³£¦³Âk¹sªº·N«ä¹À¡C
¹ï§Ú³oºØªì¾ÇªÌ¨Ó»¡¡A³oºØ°ÝÃD¤w¸g«Ü²`¶ø¤F><
Jess

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-17 07:50 ½s¿è

¦^´_ 5# jesscc
´«¤@­Ó¥N¸¹©M´¡¤Js = 0 ³o¨âªÌ¤S¦³¤°»ò®t§O©O?·Pı¦n¹³³£¦³Âk¹sªº·N«ä¹À   ¨S¦³®t§O¦ý´N¬O­nÂk¹s.

'¨ú¥X Etrusion ¥X³f©ú²Ó
Sheets("Etrusion").Activate
Dim Ay1()   
''
         ReDim Preserve Ay1(s)
       Ay1(s) = Array(Cells(i, ar1(0)).Value, Cells(i, ar1(1)).Value)
          s = s + 1
      Next
'
'¨ú¥X Metal ¥X³f©ú²Ó
µ{¦¡¶i¦æ¨ì¦¹  s=26
Dim Ay2()   
ar2 = Array("D", "H")
For j = 7 To Cells(Rows.Count, 2).End(3).Row         
       ReDim Preserve Ay2(s)                                      '  ·í j=7 ®É Ay°t¸m26­ÓµL¸ê®ÆªºÀx¦sªÅ¶¡
       Ay2(s) = Array(Cells(j, ar2(0)).Value, Cells(j, ar2(1)).Value)  'Ayªº²Ä26Àx¦sªÅ¶¡¸ê®Æ¬°¤@ºû°}¦C  ........(S=S+1)      
       s = s + 1
      Next
    Workbooks("SPECforMRPtest.xls").Activate
        With Sheets("MRP")
        .Cells(.Rows.Count, 49).End(xlUp).Offset(1, 0).Resize(s, 2) = Application.Transpose(Application.Transpose(Ay2))
'¦]¦¹Ay1()©MAy2()ªº¸ê®Æ§ÎºA´N·|¤£¦P¡A¬O³o­Ó·N«ä¶Ü?   ¤£¬Oªº
'³Ì²×¤F Ay2¾ã­Ó°}¦C¸Ì°ÑÂøªÅ¥Õ¤¸¯À¤Î¤@ºû°}¦C ¸ê®Æªº«¬ºA¤£¤@­P µLªk©ñ¦b  .Cells(.Rows.Count, 49).End(xlUp).Offset(1, 0).Resize(s, 2)
    End With

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD