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

¦C増´î

¦C増´î

³øªí¦C¼Æ¥i§_¨Ì¨Ó·½¸ê®Æ増´î¡AÁÂÁ¨ó§U¡C ½Æ»s -9801010630co2.rar (119.18 KB)

­Y¬O§Ú¦^µª¡A¨Ï±zº¡·N¡A½Ð±zÅý§Úª¾¹D¡I                  
­Y¬O§Úªº¦^ÂСA±z¤´¦³¨ä¥L¨£¸Ñ¡A¤]½Ð±z¤£¶Þ«ü±Ð¡I

TOP

ÁÂÁÂMINª©¥D
                      ¤W¥h¬Ý¤F¤å³¹ÁÙ¬O¤@ª¾¥b¸Ñ¡AµLªk¦Û¤v¸Ñ¨M°ÝÃD¡A¥i§_°µ­Ó¹ê¦CÅý§Ú¥[³t¾Ç²ß?ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ PD961A ©ó 2010-5-19 16:33 ½s¿è

¦^´_ 3# y663258


    ¤Þ¥Î®LªO¥D©w¸q¦WºÙ
http://forum.twbts.com/viewthread.php?tid=251&extra=page%3D1
§ï¦¨ A=OFFSET(Sheet1!$O$2,,,COUNTA(Sheet1!$O$2 : $O$25),3)                                
       N=OFFSET(sheet2!$B$5,COUNTA(Sheet2!$B$5 : $B$65536),)                                

Sub Macro1()        
    [A].Copy [N]'½Æ»s¨ì        
    [A].ClearContents'²M°£        
End Sub        

¥tª½±µ¨ì¶{ª©¥Dµ¹ªººô§}¤U¸ü111ªºÀÉ®×­×¥¿¥ç¥i:http://forum.twbts.com/viewthread.php?tid=251&extra=page%3D1

TOP

PD961A ÁÂÁ¥ܱиg´ú¸Õ»P§Ú·Q­nµ²ªG¦³¤£¦P¡A¥i¯à¬O§Úªí¹F¤£¦n¦A»¡©úSheet1!O2:O35565(¨C¦¸¼Æ¶q¤£¤@¼Ë)COPY¦ÜSheet2!B5¡A¦V¤U¶K¤W¨Ã増´î¦C»PSheet1!O2:O35565¦³¸ê®Æ¦C¼Æ¦P¡A ¦A±NC5:AY5 ¤½¦¡¦P®É¦V¤U¶K¤W¡C

TOP

PD961A ÁÂÁ¥ܱиg´ú¸Õ»P§Ú·Q­nµ²ªG¦³¤£¦P¡A¥i¯à¬O§Úªí¹F¤£¦n¦A»¡©úSheet1!O2:O35565(¨C¦¸¼Æ¶q¤£¤@¼Ë)COPY¦Ü ...
y663258 µoªí©ó 2010/5/19 05:46 PM





¤j·§¬O³o¼Ë¤l! ¨ä¥Lªº¥Î¿ý»s ¦]¸Ó³£¥i¥H¿ìªº¨ì...

Sub test()
    Dim lS1RowEnd As Long
    Dim lS2RowEnd As Long
   
    lS1RowEnd = Worksheets("Sheet1").Range("Q65536").End(xlUp).Row
    lS2RowEnd = Worksheets("Sheet2").Range("B5").End(xlDown).Row + 1
   
    Worksheets("Sheet2").Rows(lS2RowEnd & ":" & lS1RowEnd + lS2RowEnd - 2).Insert Shift:=xlDown
   
    Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(2, 17), Worksheets("Sheet1").Cells(lS1RowEnd, 17)).Copy Worksheets("Sheet2").Cells(lS2RowEnd, 2)
End Sub

½Æ»s -9801010630co2.rar (97.1 KB)

­Y¬O§Ú¦^µª¡A¨Ï±zº¡·N¡A½Ð±zÅý§Úª¾¹D¡I                  
­Y¬O§Úªº¦^ÂСA±z¤´¦³¨ä¥L¨£¸Ñ¡A¤]½Ð±z¤£¶Þ«ü±Ð¡I

TOP

¦^´_ 5# y663258


    ¤£ª¾¼Ó¥D¬O¤£¬O³o­Ó·N«ä
  1. Sub Ex()
  2. Dim A As Range, Ar(31), Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.    For Each A In .Range(.[P2], .[P65536].End(xlUp))
  6.    If A.Offset <> "" Then
  7.      If d(A.Value) = "" Then
  8.         d(A.Value) = A.Offset(, 1)
  9.         Else
  10.         d(A.Value) = d(A.Value) & "," & A.Offset(, 1)
  11.      End If
  12.     End If
  13.   Next
  14. End With
  15. For Each ky In d.keys
  16.    mystr = Split(d(ky), ",")
  17.        Ar(0) = ky: Ar(1) = "95-013-4-001": Ar(3) = "¤j«¬"
  18.        For i = 0 To UBound(mystr)
  19.           Ar(i + 4) = mystr(i)
  20.        Next
  21.        If d(ky) <> "" Then
  22.         Ar(24) = Mid(mystr(0), 1, 2): Ar(26) = Val(Mid(mystr(0), 1, 2)): Ar(27) = Mid(mystr(0), 1, 2)
  23.         Ar(28) = Mid(mystr(0), 1, 2) * 2: Ar(29) = 373.5: Ar(30) = Ar(28) * Ar(29) / 100000
  24.        End If
  25.    ReDim Preserve Ay(x)
  26.    Ay(x) = Ar
  27.    x = x + 1
  28.    Erase Ar
  29. Next
  30. Sheet2.[C5:Ay65536] = ""
  31. Sheet2.[B5].Resize(x, 31) = Application.Transpose(Application.Transpose(Ay))
  32. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Hsieh ª©¥DÁÂÁ¸g´ú¸Õ§¹¥þ¥¿½T,¤£¦n·NÃѧڦbªþÀɤ¤§âsheet2  z5§Yµ{¦¡º¿ar(24)»~­È¬°¸Ó¦C²Ä¤@¦ì,¥¿½T chm.rar (28.01 KB) ¬O³Ì¤j­È,¨ä¾l»Ý¨D¦pªþÀÉ»¡©ú¡A·q½Ð¨ó§U¦A¦¹·P®¦¡C

TOP

¦^´_ 8# y663258


    ¶W¹LYÄ檺¼Æ¶q­n¦Û°Ê¼W´î·|¦³§xÃø
¥ú¬OªíÀY´N­n­«Ã¸
¨ä¾l°ÝÃD½Ð°Ñ¦Ò
  1. Sub Ex()
  2. Dim A As Range, Ar(31), Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5.    For Each A In .Range(.[P2], .[P65536].End(xlUp))
  6.    If A.Offset <> "" Then
  7.      If d(A.Value) = "" Then
  8.         d(A.Value) = A.Offset(, 1)
  9.         Else
  10.         d(A.Value) = d(A.Value) & "," & A.Offset(, 1)
  11.      End If
  12.     End If
  13.   Next
  14. End With
  15. For Each ky In d.keys
  16.    mystr = Split(d(ky), ",")
  17.        Ar(0) = "²Ä¤@´Á" & ky: Ar(1) = "95-013-4-001": Ar(3) = "¤j«¬"
  18.        For i = 0 To UBound(mystr)
  19.           Ar(i + 4) = mystr(i)
  20.        Next
  21.        If d(ky) <> "" Then
  22.         Ar(24) = mystr(UBound(mystr)): Ar(26) = Val(Mid(mystr(0), 1, 2)): Ar(27) = Mid(mystr(0), 1, 2)
  23.         Ar(28) = Mid(mystr(0), 1, 2) * 2: Ar(29) = 373.5: Ar(30) = Ar(28) * Ar(29) / 100000
  24.        End If
  25.    ReDim Preserve Ay(x)
  26.    Ay(x) = Ar
  27.    x = x + 1
  28.    Erase Ar
  29. Next
  30. Sheet2.[B5:Ay65536] = ""
  31. Sheet2.[B5].Resize(x, 31) = Application.Transpose(Application.Transpose(Ay))
  32. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

HSIEH¥S
           Ar(24) = mystr(UBound(mystr)): Ar(26) = Val(Mid(mystr(0), 1, 2)): Ar(27) = Mid(mystr(0),
Ar(26)­n§ï¨úAr(24) = mystr(UBound(mystr)): ªº¥ª°_2¦ì,¦p23045¨ú23ÁÂÁ«ü¾É

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD