c_c_lai

¦^´_ 1# tomtracy
¸Õ¸Õ¬Ý¡G

1. Sub Ex()
   
2.     Dim bt As Single
   
3.    
   
4.     With Sheet1
   
5.         bt = .Range("B" & .Rows.Count).End(xlUp).Row
   
6.         If InStr(.Range("B" & bt - 5), "¤p­p") Then
   
7.             .Range("K" & bt - 5).Formula = "=SUM(K8:K" & (bt - 6) & ")"
   
8.             .Range("K" & bt).Formula = "=SUM(K" & (bt - 5) & ":K" & (bt - 1) & ")"
   
9.         End If
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 4# tomtracy

¤U¸ü (34.44 KB)

2015-8-1 18:16

§Ú¬O¥H§A¤W¦¸ªþ¤WªºÀɮ׬°Á|¨Ò¡A¦pªG§A
¦Û­qªº®æ¦¡¦³©Ò²§°Ê¡Aµ{¦¡½X§PÂ_¥çÀ³¸òÀH
§ó¥¿¡A¥H²Å¨Æ¹ê¡C

c_c_lai

¦^´_ 6# tomtracy
¥ý¼á²M¤@¤U¡A¬°¦ó§Aªºªí³æ»Ý­n³sÄò¦³¥|²Õ¡y¤p­p¡z¡A
¨C²Õ¡y¤p­p¡zªº©w¸q¬°¦ó¡H

¤U¸ü (39.71 KB)

2015-8-2 07:14

­ì¥»ªí³æ¤§³B¸Ì¡G

1. Sub Ex()
   
2.     Dim bt As Single
   
3.    
   
4.     With Sheet1
   
5.         bt = .Range("B" & .Rows.Count).End(xlUp).Row
   
6.         If InStr(.Range("B" & bt - 5), "¤p­p") Then
   
7.             .Range("K" & bt - 5).Formula = "=SUM(K8:K" & (bt - 6) & ")"
   
8.             .Range("K" & bt).Formula = "=SUM(K" & (bt - 5) & ":K" & (bt - 1) & ")"
   
9.         End If
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

¨Ì·Ó¤§«áªþ¤Wªí³æ¤§¤º®e¡A½Ð¦Û¦æ­×¥¿¬°¡G

1. Sub Ex()
   
2.     Dim bt As Single
   
3.    
   
4.     With Sheet1
   
5.         bt = .Range("B" & .Rows.Count).End(xlUp).Row
   
6.         If InStr(.Range("B" & bt - 3), "¤p­p") Then
   
7.             .Range("K" & bt - 3).Formula = "=SUM(K8:K" & (bt - 4) & ")"     '  ¤p­p
   
8.             .Range("K" & bt).Formula = "=SUM(K" & (bt - 3) & ":K" & (bt - 1) & ")"     '  Á`­p
   
9.         End If
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

«K¦³¼Æ­È¼g¤J¤F¡C

c_c_lai

§Ú¦A½Ð±ÐLai¥ý¥Í ¬°¦ó¨C¦¸¨Ï¥Î¦¹¤å¥ó®É¬°¦ó³£­n¨ì¥N½X¤º«ö¥¨¶°±Ò¥Î©O?
tomtracy µoªí©ó 2015-8-3 23:54

¦]¬°§Aªº¤p­p¨Ã«D³]©w¦b¡y©T©wÄæ¦ì¡z¤W¡A¦Ó¬O¦ì©ó¤£©w¦C¤W¡A
³q±`§A¥i¯à¦b½T©w¿é¤J§¹¦¨«á¤~·|¥[¤W¡y¤p­p¡zÄæ¡A¦b½T©w
¤u§@µ²§ô«á¦¹®É§A¤~·|¥hIJµo Ex() ¤§¬G¡C
°²³]§A¤w½T©w¡y¤p­p¡zÄæ¬O¦ì¦b¡y©T©wÄæ¦ì¡z¤W¡A¨º§A¥i¥Hª½±µ¦b
¸Ó K Äæ¡Aª½±µ¥´¤J ¡y=SUM(K##:K##)¡z¡A¤£´Nµ²¤F¡H
¦ó¶·¦A¦h¦¹¤@Á|¥hÀ³¥Î VBA ¸Ñ¨M¡I

c_c_lai

¦^´_ 13# tomtracy
©ñ¸m©ó Sheet1 (Sheet1) µ{¦¡½X¤º¡G

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim bt As Single
   
3. 
   
4. 
   
5.     bt = Range("B" & Rows.Count).End(xlUp).Row - 3  ' ¤p­p¤§«eªº¤º®e
   
6. 
   
7.     If (bt >= 8 And Target.Row < bt) And Target.Column = 11 Then  ' ·í K Äæ¦ì­È²§°Ê®É
   
8.         ThisWorkbook.Ex
   
9.     End If
   
10. End Sub

½Æ»s¥N½X

©ñ¸m©ó ThisWorkbook µ{¦¡½X¤º¡G

1. Sub Ex()
   
2.     Dim bt As Single
   
3.    
   
4.     With Sheet1
   
5.         bt = .Range("B" & .Rows.Count).End(xlUp).Row
   
6.         If bt > 8 And InStr(.Range("B" & bt - 3), "¤p­p") Then
   
7.             .Range("K" & bt - 3).Formula = "=SUM(K8:K" & (bt - 4) & ")"     '  ¤p­p
   
8.             .Range("K" & bt).Formula = "=SUM(K" & (bt - 3) & ":K" & (bt - 1) & ")"     '  Á`­p
   
9.         End If
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

Àx¦s«á¡A¥u­n±q K8 Äæ¶}©l¦Ü¤p­p«e K Ä椺®e¦³©Ò²§°Ê®É¡A¤p­p»PÁ`­p¤º®e­È«K·|¦Û°ÊÀH¤§²§°Ê¡A
¦Ó¤£»Ý¦A¥Ñ¤H¤u±Ò°Ê Ex()¡A³o¯Âºé¬OÃi¤Hªºµ{¦¡¼gªk¥ç¬O§A©Ò´Á±æªº¤è¦¡¡C

c_c_lai

¦^´_ 15# tomtracy

¤U¸ü (80.9 KB)

2015-8-5 06:46