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

[µo°Ý] ¨Ï¥Î VBA ¤ñ¹ï¨â­ÓÄæ¦ìªº¤º®e¬O§_¬Û¦P

[µo°Ý] ¨Ï¥Î VBA ¤ñ¹ï¨â­ÓÄæ¦ìªº¤º®e¬O§_¬Û¦P

¦U¦ì¤j¤j¡A¦]§Ú­è±µÄ² VBA ¤£¤[¡A¦ý¤S«æ»Ý¼g­Ó¤pµ{¦¡¨ÓÀ³¥I¤é±`¤u§@¡A¬G·Q½Ð¦U¦ì¤j¤jÀ°¦£¡A¤£³Ó·P¿E :D ¡A©³¤U¬Oµ{¦¡©Ò»Ýªº¤º®e´y­z¡A¸ê®ÆÀɮצpªþ¥ó¤º®e¡C

1¡B§PÂ_¡i³ø»ù¼i¾ú¡jªº¬¡­¶¦WºÙ¬O§_¦s¦b¡A­Y¤£¦s¦b«h«Ø¥ß¤@­Ó¡A­Y¦s¦b«h¸õ¦Ü¸Ó¬¡­¶¡C
2¡B½Æ»s¡iGFF-ITO¡j¬¡­¶¤ºªº O3 ~ P5 ªº¤º®e¡C
3¡B¿ï¾Ü¡i³ø»ù¼i¾ú¡j¬¡­¶¡A²¾°Ê¦Ü A Äæ¸ê®Æªº³Ì§ÀºÝ¡A¨Ã¦A©¹¤U²¾°Ê¨â¦C¡C
4¡B±N O3 ~ P5 ªº¤º®e¶K¤W¡C
5¡B©¹¤U²¾°Ê¤@¦C¡C
6¡B½Æ»s¡iGFF-ITO¡j¬¡­¶¤ºªºÄæ¦ì¦WºÙ¹L¨Ó¡AAÄæ ='GFF-ITO'!B9¡ABÄæ='GFF-ITO'!G9¡ACÄæ='GFF-ITO'!J9¡K..µ¥¡A¨Ã¦b IÄæ¶ñ¤J»ù®t¡C
7¡B²¾°Ê¨ì D10¡A§PÂ_¤º®e¬O§_¬° ###¡A­Y¬O«hµ²§ôµ{¦¡¡C
8¡B¤ñ¹ï ¡iGFF-ITO¡j¬¡­¶ O10 ¸ò P10 ¨â­ÓÄæ¦ìªº¤º®e¡A­Y¤º®e¤£¬Ûµ¥¡A«h±N B¡BG¡BJ¡BK¡BL¡BV¡BO¡BP Ä檺¸ê®Æ½Æ»s¨ì¡i³ø»ù¼i¾ú¡j¬¡­¶¤ºªº¬Û¹ï¦ì¸m¡A­Y¬Ûµ¥«h¦A©¹¤U§PÂ_ D11 ¬O§_¬° ###¡A­Y¬O«hµ²§ôµ{¦¡¡A­Y§_«hÄ~Äò°õ¦æ²Ä 8 ­Ó°Ê§@¡C

³ø»ù¼i¾ú´ú¸ÕÀÉ-0923.zip (761.54 KB)

¦^´_ 1# julianwic

¤£ª¾¹D¹ï¤£¹ï¡I
  1. Sub ex()
  2. Dim sh As Worksheet
  3. Dim Rng As Range, Rn As Range
  4. On Error Resume Next
  5. Set sh = Sheets("³ø»ù¼i¾ú")
  6. If sh Is Nothing Then '¦pªG¨S"³ø»ù¼i¾ú"¤u§@ªí«h·s«Ø¤@­Ó
  7.     Set sh = Worksheets.Add
  8.     sh.Name = "³ø»ù¼i¾ú"
  9. End If
  10. With sh
  11.    
  12.     Sheets("GFF-ITO").Range("O3:P5").Copy .Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 2)
  13.     Set Rng = .Range("A" & .Cells(Rows.Count, 1).End(xlUp).Row + 2)
  14.     With Rng
  15.         Sheets("GFF-ITO").Range("b9:d9").Copy Rng
  16.         .HorizontalAlignment = xlCenter
  17.         .MergeCells = False
  18.         Sheets("GFF-ITO").Range("G9,J9,K9,L9").Copy .Offset(, 1)
  19.         Sheets("GFF-ITO").Range("V9").Copy .Offset(, 5)
  20.         Sheets("GFF-ITO").Range("O9,P9").Copy .Offset(, 6)
  21.         Sheets("GFF-ITO").Range("P9").Copy .Offset(, 8)
  22.         .Offset(, 8) = "»ù®t"
  23.     End With

  24.     For Each Rn In Sheets("GFF-ITO").Range("D10:D60")
  25.         aa = Rn
  26.         If Rn = "###" Then Exit Sub
  27.         If Sheets("GFF-ITO").Cells(Rn.Row, "O") <> Sheets("GFF-ITO").Cells(Rn.Row, "P") Then
  28.             sro = .Cells(Rows.Count, 1).End(xlUp).Row + 1
  29.             .Cells(sro, "A") = Rn.Value
  30.             .Cells(sro, "b") = Sheets("GFF-ITO").Cells(Rn.Row, "G")
  31.             .Cells(sro, "c") = Sheets("GFF-ITO").Cells(Rn.Row, "j")
  32.             .Cells(sro, "d") = Sheets("GFF-ITO").Cells(Rn.Row, "k")
  33.             .Cells(sro, "e") = Sheets("GFF-ITO").Cells(Rn.Row, "l")
  34.             .Cells(sro, "f") = Sheets("GFF-ITO").Cells(Rn.Row, "v")
  35.             .Cells(sro, "g") = Sheets("GFF-ITO").Cells(Rn.Row, "o")
  36.             .Cells(sro, "h") = Sheets("GFF-ITO").Cells(Rn.Row, "p")
  37.             .Cells(sro, "i") = .Cells(sro, "h") - .Cells(sro, "g")
  38.         End If
  39.     Next
  40. End With
  41. End Sub
½Æ»s¥N½X

TOP

IPK187 ¤j¤j¡A¯u¬O¤Ó·PÁ±z¤F¡A­è¤~´ú¸Õ¤F¤@¤U°õ¦æ¤W¬O¨S¦³°ÝÃDªº¡A¦ý¦³­Ó¦a¤è·Q½Ð§A¦AÀ°¦£­×§ï¤@¤U¡A¦]¬°°õ¦æªº¬¡­¶¦WºÙ¤£·|³£©T©w¬°¡iGFF-ITO¡j¡A¥i§_­×§ï¦¨Â^¨ú«ö¤U¥¨¶°®Éªº¬¡­¶Ã¯¦WºÙ¨Ó¨Ï¥Î¡AÁÂÁ¡C

TOP

¦^´_ 3# julianwic
  1. Sub ex()
  2. Dim sh As Worksheet
  3. Dim Rng As Range, Rn As Range
  4. On Error Resume Next
  5. shn = Sheets(shn).Name
  6. Set sh = Sheets("³ø»ù¼i¾ú")
  7. If sh Is Nothing Then '¦pªG¨S"³ø»ù¼i¾ú"¤u§@ªí«h·s«Ø¤@­Ó
  8.     Set sh = Worksheets.Add
  9.     sh.Name = "³ø»ù¼i¾ú"
  10. End If
  11. With sh
  12.    
  13.     Sheets(shn).Range("O3:P5").Copy .Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 2)
  14.     Set Rng = .Range("A" & .Cells(Rows.Count, 1).End(xlUp).Row + 2)
  15.     With Rng
  16.         Sheets(shn).Range("b9:d9").Copy Rng
  17.         .HorizontalAlignment = xlCenter
  18.         .MergeCells = False
  19.         Sheets(shn).Range("G9,J9,K9,L9").Copy .Offset(, 1)
  20.         Sheets(shn).Range("V9").Copy .Offset(, 5)
  21.         Sheets(shn).Range("O9,P9").Copy .Offset(, 6)
  22.         Sheets(shn).Range("P9").Copy .Offset(, 8)
  23.         .Offset(, 8) = "»ù®t"
  24.     End With

  25.     For Each Rn In Sheets(shn).Range("D10:D60")
  26.         aa = Rn
  27.         If Rn = "###" Then Exit Sub
  28.         If Sheets(shn).Cells(Rn.Row, "O") <> Sheets(shn).Cells(Rn.Row, "P") Then
  29.             sro = .Cells(Rows.Count, 1).End(xlUp).Row + 1
  30.             .Cells(sro, "A") = Rn.Value
  31.             .Cells(sro, "b") = Sheets(shn).Cells(Rn.Row, "G")
  32.             .Cells(sro, "c") = Sheets(shn).Cells(Rn.Row, "j")
  33.             .Cells(sro, "d") = Sheets(shn).Cells(Rn.Row, "k")
  34.             .Cells(sro, "e") = Sheets(shn).Cells(Rn.Row, "l")
  35.             .Cells(sro, "f") = Sheets(shn).Cells(Rn.Row, "v")
  36.             .Cells(sro, "g") = Sheets(shn).Cells(Rn.Row, "o")
  37.             .Cells(sro, "h") = Sheets(shn).Cells(Rn.Row, "p")
  38.             .Cells(sro, "i") = .Cells(sro, "h") - .Cells(sro, "g")
  39.         End If
  40.     Next
  41. End With
  42. End Sub
½Æ»s¥N½X

TOP

IPK187 ¤j¤j¡A´ú¸Õ«á¡Aµ{¦¡°õ¦æ¨ì²Ä 27 ¦æ (If Rn = "###" Then Exit Sub) ³o­Ó§PÂ_¦¡«á´N·|ª½±µ¸õ¨ì End sub¡A¤£¾å±o¬O¤°»ò°ÝÃD¡A¥i¥H³Â·Ð¦A¬Ý¤@¤U¶Ü¡HÁÂÁ±z

TOP

¦^´_ 5# julianwic


    shn = Sheets(shn).Name
³o¥y§ï¦¨
shn = ActiveSheet.Name

TOP

­×§ï«áµ{¦¡ OK ¤F¡AÁÂÁ lpk187 ¤j¤jªº¨ó§U¡I

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD