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

[µo°Ý] ¦p¦ó±Nªí³æ1+ªí³æ2 ¦X¨Ö

¦^´_ 10# Hsieh


    ÁÂÁ¤j¤jªº´£¨Ñ ¯u¼F®`
owen

TOP

¦^´_ 10# Hsieh

    DEAR ¤j¤j:
    §Ú¦³µo²{°ÝÃD ·Q½Ð±Ð;
   
   1.·í «öÁ`¦~³ø ²£¥Í ²Ä¤G­¶°_      "¤w°h¦^ ¤w¸É³f        ³Æµù"«o¨S¦³Åã¥Ü¥X¨Ó
  2.·í ¨C¦¸ ¤@ª½«ö  Á`¦~³øªº«ö¶s  A2 ªºª½·|²£¥ÍÅܤƠ ¦Ó¥B G2~I2ªºÃC¦â¤]¤@ª½½Æ»s,¥i¥H³]­p  ²M°£ A2~L65536ªº­È  ªº«ö¶s
    (§@ªk «ö¥þ³¡¦~³øªº«ö¶s «Ü¦h¦¸¼Æ¾Ú´N§ïÅÜ,¦Ó·s¼W ²M°£¶s  «ö¤@¦¸«á,¦A«ö ¥þ³¡¦~³øªº«ö¶s ´N«ì´_¥¿±`)

   ÁÂÁ¤j¤j

·~°È­û·~ÁZªí¦X¨Ö0319(´ú¸Õ).rar (124.09 KB)

owen

TOP

¦^´_ 12# owen9399
  1. Sub ¥þ³¡¤½¥qÁ`¦~³ø_«ö¶s1_Click()


  2. Dim Sh As Worksheet, A As Range, C As Range, Ay()

  3. For Each Sh In Sheets(Array("¤p«¬ªÑ", "¤j«¬ªÑ"))

  4.    With Sh

  5.      Set A = .[A:A].Find("¤½¥q§Ç¸¹", .[A65536], lookat:=xlWhole)

  6.      Do Until Application.CountA(A.Offset(, 1).Resize(, 12)) = 0

  7.          r = A.Row

  8.         r1 = .Range("A:A").Find("¦X­p", A, lookat:=xlWhole).Row

  9.         r2 = .Range("A:A").FindNext(.Cells(r1, 1)).Row

  10.        For Each C In A.Offset(, 1).Resize(, 12).SpecialCells(xlCellTypeConstants)

  11.         k = C.Column

  12.         ReDim Preserve Ay(s)

  13.        Ay(s) = Array(.Cells(r, k).Value, .Cells(r + 1, k).Value, .Cells(r1 + 1, k - 1).Value, .Cells(r1, k).Value, .Cells(r1 + 1, k + 1).Value, .Cells(r2, k).Value, "=rc6-rc5-rc10+rc11", "=if(rc5-rc6-rc10>0,0,rc6-rc5-rc10)", "=if(rc5-rc6-rc11<0,0,rc5-rc6-rc11)")

  14.         s = s + 1

  15.         Next

  16.         Set A = .Range("A:A").Find("¤½¥q§Ç¸¹", .Cells(r2, 1), lookat:=xlWhole)
  17.         
  18.      Loop

  19.    End With

  20. Next

  21. If s > 0 Then

  22. With Sheets("¥þ³¡¤½¥qÁ`¦~³ø")
  23. .UsedRange.Offset(1).Clear

  24. .[A2].Resize(s, 9) = Application.Transpose(Application.Transpose(Ay))

  25. .Range("A1").CurrentRegion.Sort key1:=.[A1], Header:=xlYes

  26. r = 42: k = 0

  27. Do Until .Cells(r, 1) = ""

  28. .Cells(r, 1).EntireRow.Insert

  29. .[A1:L1].Copy .Cells(r, 1)

  30. k = k + 1

  31. r = r + 40 + k

  32. Loop

  33. End With

  34. End If



  35. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2012-3-20 08:28): »{¯u¥h¤F¸Ñµ{¦¡½Xªº§@¥Î¤~¬O¹ï§A¦³À°§Uªº

¦^´_ 13# Hsieh


    ·P®¦ ¤j¤j,¦³§Aªº«ü¾É©M­@¤ßªº³B²z§Ú­Ì«Ü¤p³¡¥÷ªº°ÝÃD, ¯uªº«ÜÁÂÁ§A:)
owen

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD