­ã´£³¡ªL

¦^´_ 4# PJChen


r = [F65535].End(3).Row > ¬O³Ì«á¤@µ§¸ê®Æªº--¦C¸¹

r = [F65535].End(3).Row -3 > ´î¥h¼ÐÃD¦C¤W¤èªº¦C¼Æ, ¤~¬O¸ê®Æªº--µ§¼Æ


r = [F65535].End(3).Row > (3) = (xlup)

­ã´£³¡ªL

¦^´_ 1# PJChen

Range("JQ4") = "=" & "AH4-BR4"
³o¥u¬O²³æ¤½¦¡, ¨Ã¤£¤Ó¯Ó®Ä¯à,
°£«D¦æ¼Æ¤Ó¦h,
¬Æ¦Ü¤w¦³¨ä¥¦¤j¶q¤½¦¡[¥¿¦b¤Þ¥Î]JQÄæ, ©Ò¥H¤~·|©ì²Ö³t«×

­ã´£³¡ªL

.Range("B18").Resize(1, xcol) = "=IF(B$9*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))" & _
    "-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$BJ$3:$CB$3=""¦w"")*(­¸¤ñ!$BJ$4:$CB$55))>=0,""OK""," & _
    "INT(B$9*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)" & _
    "*(­¸¤ñ!$BJ$3:$CB$3=""¦w"")*(­¸¤ñ!$BJ$4:$CB$55))/SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))))"

¦r¦ê³sµ²¥Î & _
¤½¦¡¤¤¦³Âù¤Þ¸¹ªº"¦w", ¶·¥~¥[¤@¹ï""¦w""

­ã´£³¡ªL

¦^´_ 19# PJChen


Range("JQ4:JQ" & xRow).value = "=AH4-BR4"
§ï
Range("JQ4:JQ" & xRow) = "=IF(F4="""","""",AH4-BR4)"

­ã´£³¡ªL

¦^´_ 22# PJChen


¤W¶ÇÀɮצn°µ¨Æ~~

­ã´£³¡ªL

¨âºØ¤è®×, ¦Û¦æ¿ï¥Î:
Sub TEST_1()
Dim R&, Arr, Brr, i&, S&(1 To 2), V1, V2
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Arr = Range("K2:Q" & R)
Brr = Range("M2:N" & R)
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "«~¦W" Then Erase S: GoTo 101
    If Arr(i, 1) = "¦X­p" Then
       Brr(i, 1) = S(1) '½c¼Æ¦X­p
       Brr(i, 2) = S(2) '²~¼Æ¦X­p
       Erase S: GoTo 101
    End If
    Brr(i, 1) = "":    Brr(i, 2) = ""
    V1 = Val(Arr(i, 6)) '¥]¸Ë¼Æ
    V2 = Val(Arr(i, 7)) '­qÁʼÆ
    If Arr(i, 2) = "" Or V1 = 0 Then GoTo 101
    Brr(i, 1) = Int(V2 / V1) '½c¼Æ
    S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
    Brr(i, 2) = V2 Mod V1  '²~¼Æ
    S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
101: Next i
Range("M2:N" & R) = Brr
End Sub

'============================================
Sub TEST_2()
Dim R&
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
With Range("M3:N" & R)
     .Formula = "=IF($K3=$K$2,M$2,IF($K3=""¦X­p"",SUM(M$1:M2)-SUMIF($K$1:$K2,""¦X­p"",M$1:M2)*2," & _
            "IF(($L3="""")+($P3=0),"""",IF(M$2=""½c¼Æ"",INT($Q3/$P3),MOD($Q3,$P3)))))"
     .Value = .Value
End With


'=============================================
End Sub

­ã´£³¡ªL

¦^´_ 28# PJChen

Sub TEST_1()
Dim R&, Arr, Brr, i&, S&(1 To 2), V1, V2, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Arr = Range("K2:Q" & R)
Brr = Range("M2:N" & R)
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "«~¦W" Then Erase S: C = 1: GoTo 101
    If Arr(i, 1) = "¦X­p" Then
       Brr(i, 1) = S(1) '½c¼Æ¦X­p
       Brr(i, 2) = S(2) '²~¼Æ¦X­p
       Erase S: C = 0: GoTo 101
    End If
    If C = 1 Then
       Brr(i, 1) = "":    Brr(i, 2) = ""
       V1 = Val(Arr(i, 6)) '¥]¸Ë¼Æ
       V2 = Val(Arr(i, 7)) '­qÁʼÆ
       If Arr(i, 2) = "" Or V1 = 0 Then GoTo 101
       Brr(i, 1) = Int(V2 / V1) '½c¼Æ
       S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
       Brr(i, 2) = V2 Mod V1  '²~¼Æ
       S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
    End If
101: Next i
Range("M2:N" & R) = Brr
End Sub

'================================

­ã´£³¡ªL

¦^´_ 28# PJChen


Sub TEST_2()
Dim R&, xR As Range, xH As Range, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, 3): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, 4))
            .Columns(1).FormulaR1C1 = "=IF(OR(RC[-1]="""",N(RC[3])=0),"""",INT(RC[4]/RC[3]))"
            .Columns(2).FormulaR1C1 = "=IF(OR(RC[-2]="""",N(RC[2])=0),"""",MOD(RC[3],RC[2]))"
            .Value = .Value
       End With
       xR(1, 3) = "=SUM(" & Range(xH(1, 1), xR(0, 3)).Address & ")" '½c¼Æ¦X­p¤½¦¡
       xR(1, 4) = "=SUM(" & Range(xH(1, 2), xR(0, 4)).Address & ")" '²~¼Æ¦X­p¤½¦¡
       xR(1, 7) = "=SUM(" & Range(xH(1, 5), xR(0, 7)).Address & ")" '­qÁÊ¼Æ ¦X­p¤½¦¡
       C = 0
    End If
101: Next
End Sub


'=================================

­ã´£³¡ªL

¦^´_ 31# PJChen


Sub ­qÁʼÆ_²M°£()
Dim R&, xR As Range, xH As Range, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       Range(xH(1, 7), xR(0, 7)).ClearContents
       C = 0
    End If
101: Next
End Sub

Sub ¤¹¦¬¤é_¤½¦¡()
Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Fx(1) = "=IF(J3="""","""",J3-U3)"
Fx(2) = "=IF(J3="""","""",""~"")"
Fx(3) = "=IF(N(B$_X)*LEN(K3)*N(T3)*N(U3)=0,"""",B$_X+T3-1)"
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       For j = 1 To 3
           Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, xH.Row), "_X", xH.Row - 2)
       Next j
       C = 0
    End If
101: Next
End Sub

Sub ¤¹¦¬¤é_²M°£()
Dim R&, xR As Range, xH As Range, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       Range(xH, xR(0, 0)).ClearContents
       C = 0
    End If
101: Next
End Sub


'==================================

­ã´£³¡ªL

¦^´_ 33# PJChen

Sub ¤¹¦¬¤é_¤½¦¡()
Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Fx(1) = "=IF(J3="""","""",J3-U3-V3)"
Fx(2) = "=IF(J3="""","""",""~"")"
Fx(3) = "=IF(OR(B$_X="""",K3=""""),"""",B$_X+T3-1)"
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = xH.Row: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       For j = 1 To 3
           Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, C), "_X", C - 2)
       Next j
          With Range(xH, xR(0, 0)): .Value = .Value: End With
       C = 0
    End If
101: Next
End Sub


'==============================