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

¿z¿ï«á¤£ÁôÂÃ_©µ¦ù©«_½Ð«e½ú­Ì«ü¾É°}¦C­þ¸Ì³]¿ù¤F

¿z¿ï«á¤£ÁôÂÃ_©µ¦ù©«_½Ð«e½ú­Ì«ü¾É°}¦C­þ¸Ì³]¿ù¤F

¦U¦ì«e½ú¤È¦w!
1.ªþ¥ó¬O¸ÕµÛ±N ¿z¿ï«á¤£ÁôÂà ³o©«ªº¤J¥X®w¸ê®Æ¥Ñ¾î¦¡§ï¬°ª½¦¡
1.1.¥Øªº¬O­n¤À¨É¼Ó¥Dª½¦¡¸ê®Æªº¦n³B
1.2.¥D­n¥Øªº¬O­n¾Ç²ß°}¦C¤è¦¡ªº»yªk

2.¥H¤U¨â­Óµ{¦¡½X ANDY»{¬°À³¸Óµ²ªG·|¤@¼Ë!
½Ð±Ð¦U¦ì«e½ú ¬O­þ¸Ì¿ù¤F!

ÁÂÁ¦U¦ì«e½ú«ü¾É!

¤J¥X®wºÞ²z-5.zip (63.43 KB)

¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

Option Explicit
Sub ¤p­p()
Call ¥þ³¡Åã¥Ü
Call ±Æ§Ç

Dim Er&, Arr(), Brr(), i, xF, xE
[AS:AS].ClearContents
[AS13] = "¤ë_¾÷ºØ_«~¦W_®Æ¸¹"
Er = ActiveSheet.UsedRange.Rows.Count
If Range("A" & Er) = "¥ªÁäÂù«ö§R°£¦¹¦C" Then
   Rows(Er).Delete
   Er = ActiveSheet.UsedRange.Rows.Count
End If
Arr = Range([A1], Range("AO" & Er))
ReDim Brr(1 To Er - 13)
For i = 14 To UBound(Arr)
   Cells(i, 45) = Format(Cells(i, 37), "yyyy/mm") & "_¾÷ºØ:" & Cells(i, 5) & "_«~¦W:" & Cells(i, 9) & "_®Æ¸¹:" & Cells(i, 13)
Next
Range("E13:AS" & ActiveSheet.UsedRange.Rows.Count).Subtotal GroupBy:=41, Function:=xlSum, TotalList:=Array(21, 25, _
        29), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Cells.ClearOutline ' §R¤jºõ
xE = ""
For i = 14 To ActiveSheet.UsedRange.Rows.Count
   xF = Cells(i, "AS")
   If xF Like "*¦X­p*" = True Then
      Cells(i - 1, "U").Interior.ColorIndex = 35
   End If
   If xF Like "*­p*" = True Then
      Rows(i).Interior.ColorIndex = 36
      Rows(i).Value = Rows(i).Value
      GoTo 9999
   End If
   If xF <> xE Then
      If Cells(i, "Q") = "" Then
         Cells(i, "Q").Interior.ColorIndex = 3
         MsgBox Cells(i, "AS") & "¨S¦³¤W¤ë®w¦s¸ê®Æ!" & Chr(10) & Chr(10) & _
                "°õ¦æ¤¤Â_! ½Ð­×¥¿«á¦A­«·s°õ¦æ!"
         ActiveWindow.ScrollRow = i - 10
         Exit Sub
      End If
      Cells(i, "U") = ""
      Cells(i, "U") = Cells(i, "Q") + Cells(i, "Y") - Cells(i, "AC") + Cells(i, "AG")
      xE = Cells(i, "AS")
      Else
         Cells(i, "Q") = ""
         Cells(i, "U") = Cells(i - 1, "U") + Cells(i, "Q") + Cells(i, "Y") - Cells(i, "AC") + Cells(i, "AG")
         Cells(i, "U").Interior.ColorIndex = xlNone
   End If
   
9999
Next
End Sub


Option Explicit
Sub ¤p­p_ERR()
Call ¥þ³¡Åã¥Ü
Call ±Æ§Ç

Dim Er&, Arr(), Brr(), i, xF, xE
[AS:AS].ClearContents
[AS13] = "¤ë_¾÷ºØ_«~¦W_®Æ¸¹"
Er = ActiveSheet.UsedRange.Rows.Count
If Range("A" & Er) = "¥ªÁäÂù«ö§R°£¦¹¦C" Then
   Rows(Er).Delete
   Er = ActiveSheet.UsedRange.Rows.Count
End If
Arr = Range([A1], Range("AO" & Er))
ReDim Brr(1 To Er - 13)
For i = 14 To UBound(Arr)
   Brr(i - 13) = Format(Cells(i, 37), "yyyy/mm") & "_¾÷ºØ:" & Arr(i, 5) & "_«~¦W:" & Arr(i, 9) & "_®Æ¸¹:" & Arr(i, 13)
Next
[AS14].Resize(UBound(Brr)) = Brr
Range("E13:AS" & ActiveSheet.UsedRange.Rows.Count).Subtotal GroupBy:=41, Function:=xlSum, TotalList:=Array(21, 25, _
        29), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Cells.ClearOutline ' §R¤jºõ
xE = ""
For i = 14 To ActiveSheet.UsedRange.Rows.Count
   xF = Cells(i, "AS")
   If xF Like "*¦X­p*" = True Then
      Cells(i - 1, "U").Interior.ColorIndex = 35
   End If
   If xF Like "*­p*" = True Then
      Rows(i).Interior.ColorIndex = 36
      Rows(i).Value = Rows(i).Value
      GoTo 9999
   End If
   If xF <> xE Then
      If Cells(i, "Q") = "" Then
         Cells(i, "Q").Interior.ColorIndex = 3
         MsgBox Cells(i, "AS") & "¨S¦³¤W¤ë®w¦s¸ê®Æ!" & Chr(10) & Chr(10) & _
                "°õ¦æ¤¤Â_! ½Ð­×¥¿«á¦A­«·s°õ¦æ!"
         ActiveWindow.ScrollRow = i - 10
         Exit Sub
      End If
      Cells(i, "U") = ""
      Cells(i, "U") = Cells(i, "Q") + Cells(i, "Y") - Cells(i, "AC") + Cells(i, "AG")
      xE = Cells(i, "AS")
      Else
         Cells(i, "Q") = ""
         Cells(i, "U") = Cells(i - 1, "U") + Cells(i, "Q") + Cells(i, "Y") - Cells(i, "AC") + Cells(i, "AG")
         Cells(i, "U").Interior.ColorIndex = xlNone
   End If
   
9999
Next
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

®t²§³B¦p¤U:

OK:
For i = 14 To UBound(Arr)
   Cells(i, 45) = Format(Cells(i, 37), "yyyy/mm") & "_¾÷ºØ:" & Cells(i, 5) & "_«~¦W:" & Cells(i, 9) & "_®Æ¸¹:" & Cells(i, 13)
Next

ERR:
For i = 14 To UBound(Arr)
   Brr(i - 13) = Format(Cells(i, 37), "yyyy/mm") & "_¾÷ºØ:" & Arr(i, 5) & "_«~¦W:" & Arr(i, 9) & "_®Æ¸¹:" & Arr(i, 13)
Next
[AS14].Resize(UBound(Brr)) = Brr
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

Andy¤W¶Çªº½d¨ÒÀɥǤFÄY­«¿ù»~
ÁöµM«Ü¥áÁy ÁÙ¬O­n©Ó»{!
Sorry ½Ð¦U¦ìºô¤Í¤£­n¨Ï¥Î½d¨ÒÀÉ
¦pªG¥i¥Hªº¸Ü ½Ð¤p»x©Îª©¥D§R°£¦¹©«
Andy¹ï¤£°_¤j®a!

¨Ï¥Î¤p­p¤§«áªºÁ`­p¤£¯àª½±µÅܦ¨­È ·|ÄY­«¿ù»~!
If xF Like "*­p*" = True Then
      Rows(i).Interior.ColorIndex = 36
      Rows(i).Value = Rows(i).Value
      GoTo 9999
   End If
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD