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

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

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

¥»©«³Ì«á¥Ñ owen9399 ©ó 2012-3-16 15:56 ½s¿è

Dear ¦U¦ì¤j¤j:

¦p¦ó±Nªí³æ1+ªí³æ2 ¦X¨Ö

¦X¨Öªº¤º®e ·|¨Ì¶¶§Ç±Æ¦C

¦X¨Ö.rar (1.47 KB)
owen

[ª©¥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

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

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

¦^´_ 10# Hsieh


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

TOP

¦^´_ 9# 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.      Loop
  18.    End With
  19. Next
  20. If s > 0 Then
  21. With Sheets("¥þ³¡¤½¥qÁ`¦~³ø")
  22. .[A2].Resize(s, 9) = Application.Transpose(Application.Transpose(Ay))
  23. .Range("A1").CurrentRegion.Sort key1:=.[A1], Header:=xlYes
  24. r = 42: k = 0
  25. Do Until .Cells(r, 1) = ""
  26. .Cells(r, 1).EntireRow.Insert
  27. .[A1:I1].Copy .Cells(r, 1)
  28. k = k + 1
  29. r = r + 40 + k
  30. Loop
  31. End With
  32. End If
  33. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ owen9399 ©ó 2012-3-17 13:26 ½s¿è

¦^´_ 8# Hsieh


    ÁÂÁ¤j¤j

  1.§Ú·Q½Ð±Ð¤@¤U   (§Ú¤À§O ¦b ¤p«¬ªÑ¦~³ø.¤j«¬ªÑ¦~³ø.¥þ³¡¤½¥qÁ`¦~³ø¤¤)
   
   ·s¼W    ¤w°h¦^        ¤w¸É³f
  
  ¥i¥H­×§ï
  ¤p«¬ªÑ¦~³ø+¤j«¬ªÑ¦~³ø¦X¨Ö  ,¥þ³¡¤½¥qÁ`¦~³ø§â(A~F)ªº½d³ò½Æ»s¨Ã¨Ì¶¶§Q±Æ¦C

  ¦Ó  Á`°h¦^~¤w¸É³f ¤£¥Î³B²z (§Ú¦Û¤v­n¥Î­pºâ³B²z)
   
   ¦Ó¥B, ¥þ³¡¤½¥qÁ`¦~³ø,¨C40®a¤½¥q±Æ¦¨ 1¤j­¶
                      ²Ä41®a¤½¥q  ¬°²Ä2­¶°_

  2.¦p¦ó ¿é¤J ¤p«¬ªÑ©Î¤j«¬ªÑ¸ê®Æ  ¦Û°Ê¦s¤J  ¤p«¬ªÑ©Î¤j«¬ªÑªº¦~³ø¤¤  (¦]¬°§Ú¬O¥Î Àx¦s®æµ¥©ó¤è¦¡¤ñ¸û¶O®É)
     ¤]¬O ¨C¤@­¶ ¦@ 40®a¤½¥q

  «D±`·PÁÂ
  3.§Ú«ö¦n´X¤U ¥þ³¡¤½¥qÁ`¦~³ø  ªº«ö¶s®É,¥i¥H³]­p ¤£¼vÅT Á`°h¦^~¤w¸É³f ªº¤½¦¡¶Ü?
      ¦]¬° ·|¦Û°ÊÂл\

·~°È­û·~ÁZªí¦X¨Ö0318.rar (120.1 KB)
owen

TOP

¦^´_ 6# owen9399
  1. Sub All_Paper() '¥þ³¡¦~³ø
  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, .Cells(r2 + 1, k).Value)
  14.         s = s + 1
  15.         Next
  16.      Set A = .Range("A:A").Find("¤½¥q§Ç¸¹", .Cells(r2, 1), lookat:=xlWhole)
  17.      Loop
  18.    End With
  19. Next
  20. If s > 0 Then
  21. With Sheets("¥þ³¡¤½¥qÁ`¦~³ø")
  22. .[A2].Resize(s, 7) = Application.Transpose(Application.Transpose(Ay))
  23. .Range("A1").CurrentRegion.Sort key1:=.[A1], Header:=xlYes
  24. End With
  25. End If
  26. End Sub
  27. Sub S_Paper() '¤p«¬ªÑ¦~³ø
  28. Dim A As Range, C As Range, Ay()
  29.    With Sheets("¤p«¬ªÑ")
  30.      Set A = .[A:A].Find("¤½¥q§Ç¸¹", .[A65536], lookat:=xlWhole)
  31.      Do Until Application.CountA(A.Offset(, 1).Resize(, 12)) = 0
  32.         r = A.Row
  33.         r1 = .Range("A:A").Find("¦X­p", A, lookat:=xlWhole).Row
  34.         r2 = .Range("A:A").FindNext(.Cells(r1, 1)).Row
  35.         For Each C In A.Offset(, 1).Resize(, 12).SpecialCells(xlCellTypeConstants)
  36.         k = C.Column
  37.         ReDim Preserve Ay(s)
  38.         Ay(s) = Array(.Cells(r, k).Text, .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, .Cells(r2 + 1, k).Value)
  39.         s = s + 1
  40.         Next
  41.      Set A = .Range("A:A").Find("¤½¥q§Ç¸¹", .Cells(r2, 1), lookat:=xlWhole)
  42.      Loop
  43.    End With
  44. If s > 0 Then
  45. With Sheets("¤p«¬ªÑ¦~³ø")
  46. .[A2].Resize(s, 7) = Application.Transpose(Application.Transpose(Ay))
  47. .Range("A1").CurrentRegion.Sort key1:=.[A1], Header:=xlYes
  48. End With
  49. End If
  50. End Sub
  51. Sub U_Paper() '¤j«¬ªÑ¦~³ø
  52. Dim A As Range, C As Range, Ay()
  53.    With Sheets("¤j«¬ªÑ")
  54.      Set A = .[A:A].Find("¤½¥q§Ç¸¹", .[A65536], lookat:=xlWhole)
  55.      Do Until Application.CountA(A.Offset(, 1).Resize(, 12)) = 0
  56.         r = A.Row
  57.         r1 = .Range("A:A").Find("¦X­p", A, lookat:=xlWhole).Row
  58.         r2 = .Range("A:A").FindNext(.Cells(r1, 1)).Row
  59.         For Each C In A.Offset(, 1).Resize(, 12).SpecialCells(xlCellTypeConstants)
  60.         k = C.Column
  61.         ReDim Preserve Ay(s)
  62.         Ay(s) = Array(.Cells(r, k).Text, .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, .Cells(r2 + 1, k).Value)
  63.         s = s + 1
  64.         Next
  65.      Set A = .Range("A:A").Find("¤½¥q§Ç¸¹", .Cells(r2, 1), lookat:=xlWhole)
  66.      Loop
  67.    End With
  68. If s > 0 Then
  69. With Sheets("¤j«¬ªÑ¦~³ø")
  70. .[A2].Resize(s, 7) = Application.Transpose(Application.Transpose(Ay))
  71. .Range("A1").CurrentRegion.Sort key1:=.[A1], Header:=xlYes
  72. End With
  73. End If
  74. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# owen9399
  1. Sub ¥þ³¡¤½¥qÁ`¦~³ø_«ö¶s1_Click()
  2. Application.ScreenUpdating = False
  3. With Sheets("¥þ³¡¤½¥qÁ`¦~³ø")
  4.   .Columns("A:J") = ""
  5.   Sheets("¤p«¬ªÑ¦~³ø").Range("A1:J" & Sheets("¤p«¬ªÑ¦~³ø").[J65536].End(xlUp).Row).Copy
  6.   .[A1].PasteSpecial xlPasteValues
  7.   Sheets("¤j«¬ªÑ¦~³ø").Range("A2:J" & Sheets("¤j«¬ªÑ¦~³ø").[J65536].End(xlUp).Row).Copy
  8.   .[A65536].End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  9.   LR = .UsedRange.Rows.Count
  10.   For R = LR To 2 Step -1
  11.     If .Cells(R, 1) = .Cells(1, 1) Or .Cells(R, 1) = O Or Application.CountA(.Rows(R)) = 0 Then
  12.       .Rows(R).Delete
  13.     End If
  14.   Next R
  15.   .[A1].CurrentRegion.Sort Key1:=.[A1], Order1:=xlAscending, Header:=xlGuess
  16. End With
  17. Application.ScreenUpdating = True
  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# register313


    °ÝÃD:   ¤p«¬ªÑªº¦~³ø+¤j«¬ªÑªº¦~³ø ¨Ã¨S¦³¦X¨Ö,¥B«ö·Ó¶¶§Ç
          ¦]¬° ¨C¤@­¶ ¦@40®a¤½¥q ,¦]¦¹·|­«´_    ªíÀY
      ¤½¥q§Ç¸¹        ¤½¥q        Á`±i¼Æ        Á`ªÑ¼Æ        Á`À³¥I        Á`¤w¥I(¤½¥q¶i³f)        Á`°h¦^        ¦h»â        ©|¤í        ³Æµù
owen

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD