PJChen

¦^´_ 60# ­ã´£³¡ªL

½Ð°Ý­ã¤j,
¨C­Óªí®æ¤ºªº±Æ§Ç,¯à°÷¾ã¦C±Æ§Ç¶Ü?
¥u¦³F:Qªº½d³ò±Æ§Ç,ªí®æ¥~ªº¸ê®Æ,¦³¨Ç¬Oªí®æ¤ºªº¯Á¤Þ,¤£¸òµÛ±Æ§ÇÅܰʪº¸Ü,¾ã­Ó¸ê®Æ·|¶Ã±¼¡I

¥t¥~´ú¸Õµ²ªG:
F320¥HªÅ¥Õ®æ´ú¸Õ,±Æ§Ç«á·|¦b³Ì¤U¤è,¦ý¤¤¶¡·|ªÅ¤@¦C,¨ä¥L¥ÎªÅ®æ´ú¸Õ®É,¥¼µo²{¬Û¦P±¡§Î

²z³f³æ3.rar (324.35 KB)

¤U¸ü¦¸¼Æ: 2

2020-5-23 21:38

­ã´£³¡ªL

¦^´_ 61# PJChen


Sub ²z³f±Æ§Ç()
Dim R&, xR As Range, xH As Range, C%, V&
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Application.ScreenUpdating = False
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2): C = 1: V = 0: GoTo 101
    V = V + Val(xR(1, -4))
    If xR = "¦X­p" Then
       If C = 0 Or V = 0 Then GoTo 101
       Range(xH(1, -4), xR(0, -4)).Replace "", "ZZ", Lookat:=xlWhole '±NªÅ¦r²Å¨ú¥N¬°"ZZ"
       With Range(Rows(xH.Row), Rows(xR(0).Row))
           .Sort Key1:=.Cells(1, "F"), Order1:=xlAscending, _
                 Key2:=.Cells(1, "L"), Order2:=xlAscending, Header:=xlNo
       End With
       Range(xH(1, -4), xR(0, -4)).Replace "ZZ", "", Lookat:=xlWhole  '±N"ZZ¨ú¥N¬°ªÅ
       C = 0: V = 0
    End If
101: Next
End Sub

FÄ榳ªÅ¦r²Å""¦b°µ©Ç, ¾É­P±Æ§Ç°ÝÃD, ¥Î"¨ú¥N"¸Ñ¨M, ­YFÄ欰¤½¦¡, «Ü³Â·Ð~~

PJChen

¦^´_ 62# ­ã´£³¡ªL

­ã¤j,
´ú¸Õµ²ªG¤w¥i¥¿±`°õ¦æ,ÁÂÁ¡I

PJChen

¦^´_ 58# jcchiang

·PÁ¦ʦ£¤¤©âªÅÀ°¦£,³o´X¤Ñ´ú¸Õµ{¦¡¤w¥i¹B§@¡I

PJChen

¦^´_ 62# ­ã´£³¡ªL

­ã¤j¦n,

²z³f³æ¨Ì»Ý¨D¼W¥[¤p­pÄæ¦ì«á,µLªk¥¿±`¹B§@,¥i§_À°¦£¬Ý¬Ý!   ²z³f³æ_¨Ò¥~.rar (24.08 KB)

¤U¸ü¦¸¼Æ: 2

2020-5-28 23:53

»Ý­n­×§ï¥H¤U
Sub ¦Û°Ê½c²~()
1) ¤p­p¥[Á` ½c.²~
2) ¦X­p¥[Á` ½c.²~ (¦ý¤£¯à­«ÂÐ¥[Á`¤p­pªº­È)

Sub ¤¹¦¬¤é¤½¦¡
3) ¹J¤p­pÄæ¦ì®É,H & JÄæ ¤¹¦¬­nªÅ¥Õ

Sub ²z³f³æ­q³æ­È¤Æ
4) ¤]¦]¬°¦³¤F¤p­pÄæ¦ì,·|§â¤p­pµ¹Âл\,¦p¦óÅý¥¦¹J¤p­pÄæ¦ì®É,¤£­nÂл\¤p­pÄæ¦ì?

1. Sub ¦Û°Ê½c²~()
   
2. Dim R&, arr, Brr, i&, S&(1 To 2), V1, V2, C%
   
3. R = Cells(Rows.Count, "K").End(xlUp).Row
   
4. If R <= 2 Then Exit Sub
   
5. arr = Range("K2:Q" & R)  '®Æ¸¹~¼Æ¶qÄæ
   
6. Brr = Range("M2:N" & R)  '½c¼Æ~²~¼ÆÄæ
   
7. For i = 1 To UBound(arr)
   
8.     If arr(i, 1) = "«~¦W" Then Erase S: C = 1: GoTo 101
   
9.     If arr(i, 1) = "¦X­p" Then
   
10.        Brr(i, 1) = S(1) '½c¼Æ¦X­p
    
11.        Brr(i, 2) = S(2) '²~¼Æ¦X­p
    
12.        Erase S: C = 0: GoTo 101
    
13.     End If
    
14.     If C = 1 Then
    
15.        Brr(i, 1) = "":    Brr(i, 2) = ""
    
16.        V1 = Val(arr(i, 6)) '¥]¸Ë¼Æ
    
17.        V2 = Val(arr(i, 7)) '­qÁʼÆ
    
18.        If arr(i, 2) = "" Or V1 = 0 Then GoTo 101
    
19.        Brr(i, 1) = Int(V2 / V1) '½c¼Æ
    
20.        S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
    
21.        Brr(i, 2) = V2 Mod V1  '²~¼Æ
    
22.        S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
    
23.     End If
    
24. 101: Next i
    
25. Range("M2:N" & R) = Brr
    
26. End Sub

½Æ»s¥N½X

1. Sub ¤¹¦¬¤é¤½¦¡()
   
2. Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
   
3. R = Cells(Rows.Count, "K").End(xlUp).Row
   
4. If R <= 2 Then Exit Sub
   
5. Fx(1) = "=IF(J3="""","""",J3-U3-V3+1)" '¤¹¦¬(°_)
   
6. Fx(2) = "=IF(J3="""","""",""~"")"
   
7. Fx(3) = "=IF(OR(B$_X="""",K3=""""),"""",B$_X+T3-2)" '¤½¦¡(¨´)
   
8. For Each xR In Range("K2:K" & R)
   
9.     If xR = "«~¦W" Then Set xH = xR(2, -2): C = xH.Row: GoTo 101
   
10.     If xR = "¦X­p" Then
    
11.        If C = 0 Then GoTo 101
    
12.        For j = 1 To 3
    
13.            Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, C), "_X", C - 2)
    
14.        Next j
    
15. '          With Range(xH, xR(0, 0)): .Value = .Value: End With
    
16.             With Range(xH, xR(0, 0)): .Calculate: .Value = .Value: End With
    
17.        C = 0
    
18.     End If
    
19. 101: Next
    
20. End Sub

½Æ»s¥N½X

1. Sub ²z³f³æ­q³æ­È¤Æ()
   
2. Dim Sh As Worksheet
   
3.     Set Sh = Workbooks("²z³f³æ_¨Ò¥~.xlsx").Sheets("ÂA")
   
4.             With Sh
   
5.                 Sh.Activate
   
6.                     .Range("¶KÂA­¹") = "=SUMIFS(ºô³æ!$I:$I,ºô³æ!$C:$C,ÂA!$C3,ºô³æ!$K:$K,ÂA!$B3)+IF(ÂA!$R$1=ÂA!$B$1,ÂA!$R3,0) 'QÄæ
   
7.                     .Range("¶KÂA­¹").Value = Range("¶KÂA­¹").Value
   
8.             End With
   
9. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

¦^´_ 65# PJChen


Xl0000190(½c²~¤¹¦¬¤é)v01.rar (27.21 KB)

¤U¸ü¦¸¼Æ: 2

2020-5-30 13:17

1) ¤¹¦¬¤é«ç»òºâªº, ¦Û¦æ­×§ï
2) ²z³f¤½¦¡, ª©¥»¤£¦X, °µ¤£¤F

PJChen

¦^´_ 66# ­ã´£³¡ªL

ÁÂÁ­ã¤j,
°õ¦æ¨S°ÝÃD

PJChen

¦^´_ 51# jcchiang

±z¦n,
­ìµ{¦¡¼gªk³£¬O±q²Ä3¦C¶}©l§ó·s¤½¦¡,ÀHµÛ¸ê®Æ«ùÄò¼W¥[,
§ó·s®É¶¡¶V¨Ó¶Vªø,¥B¨ç¼Æ¼g¦¨ªº¤½¦¡«Ü¤¾ªø,·Q§ï¬°«D¨ç¼Æªº¼gªk,
ÀɮרC¦¸§ó·s«á,¤½¦¡«K­È¤Æ,°£«D¦³Åܰʸê®Æ,§_«h¤£»Ý­n¨C¦¸³£±q²Ä3¦C¶}©l§ó·s¤½¦¡,
©Ò¥H§Ú¦b"VBA".sheetªº[AA3]«ü©w¤@­Ó¤é´Á,·íBÄæ>=³o­Ó¤é´Áªº¸ê®Æ¤~»Ý­n§ó·s,
§Ú¨Ì·Ó³o­Ó¼Ò¦¡,§ï¤F²Ä¤@­Ó"¤ë¥÷"Äæ,°õ¦æ¨S°ÝÃD,¦ý­n®M¨ì¥H¤U11ºØ¤£¦P¤½¦¡ªº¼gªk,
If xR = "¤ë¥÷" Or xR = "±ÄÁʳ渹½X" Or xR = "µ²¾l" Or xR = "¤j" Or xR = "¬ü"  Or xR = "¤j¤¤«n°Ï" Or xR = "¬ü¤¤«n°Ï" Or xR = "¬£ªO¹ïÀ³³æ¾Ú¤é" Or xR = "¬£ªO-¥æªO®t²§" Or xR = "¬£ªOµ²¾l" Or xR = "½LÂI®t²§"
«o¤£ª¾¦p¦ó¤U¤â,¥H¤U¬O­ìµ{¦¡, ¥i§_À°¦£¬Ý¤U,­n¦p¦ó­×§ï?   ¥þ¬Ù®Ö¾P©ú²Ó.rar (35.18 KB)

¤U¸ü¦¸¼Æ: 6

2020-6-21 12:56

1. Sub ¥_°Ï_¤½¦¡§ó·s()
   
2. Dim Sh As Worksheet, xS As Worksheet, xR
   
3. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
   
4. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
   
5. d = xS.[AA3] 'Date
   
6. Sh.Activate
   
7. '------------ 'A ¨ú¥XBÄæ¦~.¤ë,³o¬q¬O¨Ì·Ó±zªºµ{¦¡­×§ï«áªº
   
8. For Each xR In Range([b3], [b65535].End(3)) '¦V¤W End(3) = End(xlup).Row
   
9.     If xR >= d Then
   
10.         x = Year(xR) & ".." & Month(xR)
    
11.         xR.Offset(, -1) = x
    
12.     End If
    
13. Next
    
14. '------------
    
15. xRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
    
16. Range("E3:E" & xRow).Formula = "=IF(¥_°Ï!$R3="","µL¥æ³f",¥_°Ï!$T3&¥_°Ï!$S3&¥_°Ï!$R3)"
    
17. Range("E3:E" & xRow).Value = Range("E3:E" & xRow).Value
    
18. 
    
19. Range("K3:K" & xRow).Formula = "=K$2+SUM(G$3:G3)-SUM(F$3:F3)-SUM(H$3:I3)+SUM(J$3:J3)" 'µ²¾l
    
20. Range("K3:K" & xRow).Value = Range("K3:K" & xRow).Value
    
21. 
    
22. Range("L3:L" & xRow).Formula = "=L$2+SUMIF($C$3:$C3,L$1,$G$3:$G3)-SUMIF($C$3:$C3,L$1,$F$3:$F3)+SUM(J$3:J3)-SUM(N$3:N3)"
    
23. Range("L3:L" & xRow).Value = Range("L3:L" & xRow).Value
    
24. 
    
25. Range("M3:M" & xRow).Formula = "=M$2+SUMIF($C$3:$C3,M$1,$G$3:$G3)-SUMIF($C$3:$C3,M$1,$F$3:$F3)-SUM(O$3:O3)"
    
26. Range("M3:M" & xRow).Value = Range("M3:M" & xRow).Value
    
27. 
    
28. Range("N3:N" & xRow).Formula = "=IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¤j",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¤j",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¤j",«n°Ï!$A:$A,¥_°Ï!$B5),"")))"
    
29. Range("N3:N" & xRow).Value = Range("N3:N" & xRow).Value
    
30. 
    
31. Range("O3:O" & xRow).Formula = "=IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¬ü",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¬ü",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¬ü",«n°Ï!$A:$A,¥_°Ï!$B5),"")))"
    
32. Range("O3:O" & xRow).Value = Range("O3:O" & xRow).Value
    
33. 
    
34. Range("U3:U" & xRow).Formula = "=IF(OR($D3=""¤¤©M"",$D3=""¤º´ò"",$D3=""¦Á¤î""),$B3,$B3+1)" '¬£ªO¹ïÀ³³æ¾Ú¤é
    
35. Range("U3:U" & xRow).Value = Range("U3:U" & xRow).Value
    
36. 
    
37. Range("V3:V" & xRow).Formula = "=IF(COUNTIFS(B$3:B3,B3,D$3:D3,D3)=1,SUMIFS(X:X,B:B,B3,D:D,D3)-SUMIFS(F:F,B:B,V3,D:D,D3),0)" '¬£ªO-¥æªO¼Æ®t²§
    
38. Range("V3:V" & xRow).Value = Range("V3:V" & xRow).Value
    
39. 
    
40. Range("X3:X" & xRow).Formula = "=Y$2+SUM($G$3:$G3)+SUM(J$3:J3)-(SUM($H$3:$H3)+SUM($I$3:$I3)+SUM($X$3:$X3))" '¬£ªOµ²¾l
    
41. Range("X3:X" & xRow).Value = Range("X3:X" & xRow).Value
    
42. 
    
43. Range("Y3:Y" & xRow).Formula = "=½LÂI®t²§"
    
44. Range("Y3:Y" & xRow).Value = Range("Y3:Y" & xRow).Value
    
45. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 51# jcchiang

±z¦n,
ªí®æ¤W¶Ç®É¦³¤p¤pÅܰʤF®æ¦¡,¤½¦¡§Ñ¤F§ï&¦³³¡¥÷µ{¦¡¤w§ï¦¨§Ú·Q­nªº°õ¦æ¤è¦¡,¤£¹L¤£ª¾¬O¼gªk¤£¦n,©Î¸ê®Æ¤Ó¦h,¶]±o¦³ÂIºC,
¸Õ¹L³æ¤@Äæ±q¦~ªì¸ê®Æ¶}©l§ó·s,µ²ªGºC±o¹³·í¾÷¤@¼Ë,¦pªG¯à«ü¾É¤U§ó§Öªº¼gªk,´N¤Ó¦n¤F¡I
·s¼g¥hÁöµM§¹¦¨,¦ý§Ú¤£ª¾¦p¦ó§ï¬°°}¦C,©Ò¥H¬O¨CÄ檺§ó·s¤À¶}¼g¡I

1. Sub ¥_°Ï_A_¨ú¦~¤ë()
   
2. Dim Sh As Worksheet, xS As Worksheet, xR
   
3. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
   
4. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
   
5. d = xS.[AA3] 'Date
   
6. Sh.Activate
   
7. '------------ 'A ¨úBÄæ¦~.¤ë
   
8. For Each xR In Range([b3], [b65535].End(3)) '¦V¤W End(3) = End(xlup).Row
   
9.     If xR >= d Then
   
10.         xR.Offset(, -1) = Year(xR) & ".." & Month(xR)
    
11.     End If
    
12. Next
    
13. End Sub
    
14. 
    
15. Sub ¥_°Ï_E_±ÄÁʳ渹½X()
    
16. Dim Sh As Worksheet, xS As Worksheet, xR
    
17. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
    
18. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
    
19. d = xS.[AA3] 'Date
    
20. Sh.Activate
    
21. '------------ 'E ±ÄÁʳ渹½X
    
22. For Each xR In Range([b3], [b65535].End(3))
    
23.     If xR.Offset(, 16) = "" Then 'RÄæµL³æ¸¹
    
24.     xR.Offset(, 3) = "µL¥æ³f"
    
25.     End If
    
26.     If xR >= d And xR.Offset(, 16) <> "" Then
    
27.     xR.Offset(, 3) = xR.Offset(, 18) & xR.Offset(, 17) & xR.Offset(, 16) 'T&S&R
    
28.     End If
    
29. Next
    
30. End Sub
    
31. 
    
32. Sub ¥_°Ï_K_µ²¾l()
    
33. Dim Sh As Worksheet, xS As Worksheet, xR
    
34. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
    
35. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
    
36. d = xS.[AA3] 'Date
    
37. Sh.Activate
    
38. '------------ 'K µ²¾l
    
39. For Each xR In Range([b3], [b65535].End(3))
    
40.     If xR >= d Then 'k+g-f-h-i+j
    
41.         xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
    
42.     End If
    
43. Next
    
44. End Sub
    
45. 
    
46. Sub ¥_°Ï_L_¤j_µ²¾l()
    
47. Dim Sh As Worksheet, xS As Worksheet, xR
    
48. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
    
49. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
    
50. d = xS.[AA3] 'Date
    
51. Sh.Activate
    
52. '------------ 'L ¤j,µ²¾l
    
53. For Each xR In Range([b3], [b65535].End(3))
    
54.     If xR >= d And xR.Offset(, 1) = "¤j" Then 'l+g-f+j-n
    
55.         xR.Offset(, 10) = xR.Offset(-1, 10) + xR.Offset(, 5) - xR.Offset(, 4) + xR.Offset(, 8) - xR.Offset(, 12)
    
56.         Else 'l+j-n
    
57.         xR.Offset(, 10) = xR.Offset(-1, 10) + xR.Offset(, 8) - xR.Offset(, 12)
    
58.     End If
    
59. Next
    
60. End Sub
    
61. 
    
62. Sub ¥_°Ï_M_¬ü_µ²¾l()
    
63. Dim Sh As Worksheet, xS As Worksheet, xR
    
64. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
    
65. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
    
66. d = xS.[AA3] 'Date
    
67. Sh.Activate
    
68. '------------
    
69. For Each xR In Range([b3], [b65535].End(3))
    
70.     If xR >= d And xR.Offset(, 1) = "¬ü" Then 'm+g-f-o
    
71.         xR.Offset(, 11) = xR.Offset(-1, 11) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 13)
    
72.         Else 'm-o
    
73.         xR.Offset(, 11) = xR.Offset(-1, 11) - xR.Offset(, 13)
    
74.     End If
    
75. Next
    
76. End Sub
    
77. 
    
78. Sub ¥_°Ï_U_¬£ªO¹ïÀ³³æ¾Ú¤é()
    
79. Dim Sh As Worksheet, xS As Worksheet, xR
    
80. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
    
81. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
    
82. d = xS.[AA3] 'Date
    
83. Sh.Activate
    
84. '------------ 'U'¬£ªO¹ïÀ³³æ¾Ú¤é
    
85. For Each xR In Range([b3], [b65535].End(3))
    
86.     If xR >= d And (xR.Offset(, 2) = "¤¤©M" Or xR.Offset(, 2) = "¤º´ò" Or xR.Offset(, 2) = "¦Á¤î") Then
    
87.         xR.Offset(, 19) = xR
    
88.         Else
    
89.         xR.Offset(, 19) = xR + 1
    
90.     End If
    
91. Next
    
92. End Sub
    
93. 
    
94. Sub ¥_°Ï_X_¬£ªOµ²¾l()
    
95. Dim Sh As Worksheet, xS As Worksheet, xR
    
96. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
    
97. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
    
98. d = xS.[AA3] 'Date
    
99. Sh.Activate
    
100. '------------ 'X'¬£ªOµ²¾l g+j-h-i-w
     
101. For Each xR In Range([b3], [b65535].End(3))
     
102.     If xR >= d Then 'x+g+j-h-i-w
     
103.         xR.Offset(, 22) = xR.Offset(-1, 22) + xR.Offset(, 5) + xR.Offset(, 8) - xR.Offset(, 6) - xR.Offset(, 7) - xR.Offset(, 21)
     
104.     End If
     
105. Next
     
106. End Sub
     
107. 
     
108. Sub ¥_°Ï_Y_½LÂI®t²§()
     
109. Dim Sh As Worksheet, xS As Worksheet, xR
     
110. Set xS = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
     
111. Set Sh = Workbooks("¥þ¬Ù®Ö¾P©ú²Ó.xlsx").Sheets("¥_°Ï")
     
112. d = xS.[AA3] 'Date
     
113. Sh.Activate
     
114. '------------ 'Y'½LÂI®t²§
     
115. For Each xR In Range([b3], [b65535].End(3))
     
116.     If xR >= d And xR.Offset(, 24) = "" Then
     
117.         xR.Offset(, 23) = ""
     
118.         Else 'z-x
     
119.         xR.Offset(, 23) = xR.Offset(, 24) - xR.Offset(, 22)
     
120.     End If
     
121. Next
     
122. End Sub

½Æ»s¥N½X

¥Ø«e¥u³Ñ3Ä檺¤½¦¡,¦]¥]§t¤Fcountifªº¨ç¼Æ,¬d¤F¨Ç¸ê®Æ,¨S§ä¨ìÃö©ócountifªº¨ç¼Æ¦p¦ó§ï¬°VBAªº¼gªk¡I

1. Range("N3:N" & xRow).Formula = "=¥_°Ï_¤j_¤¤«n°Ï" '=IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¤j",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¤j",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¤j",«n°Ï!$A:$A,¥_°Ï!$B5),"")))
   
2. Range("N3:N" & xRow).Value = Range("N3:N" & xRow).Value
   
3. 
   
4. Range("O3:O" & xRow).Formula = "=¥_°Ï_¬ü_¤¤«n°Ï" '=IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=1,SUMIFS(¥x¤¤!$D:$D,¥x¤¤!$B:$B,"¬ü",¥x¤¤!$A:$A,¥_°Ï!$B3),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=2,SUMIFS(·s¦Ë!$D:$D,·s¦Ë!$B:$B,"¬ü",·s¦Ë!$A:$A,¥_°Ï!$B4),IF(COUNTIF(¥_°Ï!$B$3:$B3,¥_°Ï!$B3)=3,SUMIFS(«n°Ï!$D:$D,«n°Ï!$B:$B,"¬ü",«n°Ï!$A:$A,¥_°Ï!$B5),"")))
   
5. Range("O3:O" & xRow).Value = Range("O3:O" & xRow).Value
   
6. 
   
7. Range("V3:V" & xRow).Formula = "=IF(COUNTIFS(B$3:B3,B3,D$3:D3,D3)=1,SUMIFS(W:W,B:B,B3,D:D,D3)-SUMIFS(F:F,B:B,U3,D:D,D3),0)" '¬£ªO-¥æªO®t²§
   
8. Range("V3:V" & xRow).Value = Range("V3:V" & xRow).Value

½Æ»s¥N½X

jcchiang

¦^´_ 69# PJChen

¤£¬O«ÜÀ´§Aªº·N«ä!!
¤£¬O¥u¬O±N¤½¦¡©ñ¤JÄæ¦ì­pºâ¶Ü???
«á­±ªºµ{¦¡¤S§ï¦¨¨C­Ó¶µ¥Ø³æ¿W°õ¦æ???