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

[µo°Ý] «ü©w¤é´Á­pºâµ²¾l¼Æ

[µo°Ý] «ü©w¤é´Á­pºâµ²¾l¼Æ

¤j¤j¦n,
½Ð±Ð
¥_°Ïªº¸ê®Æ¬O¾ã¦~«×ªº,¸ê®Æ·|¤£Â_¼W¥[,
BÄ檺¤é´Á>=VBA¤u§@ªíªºAF2
«h­pºâKÄ檺µ²¾l¼Æ
K3=k2+g3-f3-h3-i3+j3
­ìµ{¦¡¼gªk¬O
For Each xR In Range([B3], [b65535].End(3))
    If xR >= d Then 'k+g-f-h-i+j
        xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
    End If
Next
µ²ªG·|Åܦ¨­È,¦ý¦³®É»Ý­n«O¯d¤½¦¡,
¨Ã¥B§Ú³oºØ¼gªk,ı±o¤£¦n¥Î,
½Ð°Ý¤j¤j,¦p¦ó§ïµ½µ{¦¡?
»Ý­n¦³2ºØ¼gªk
1. ­pºâ«áÅܦ¨­È
2. ­pºâ«á«O¯d¤½¦¡
®Ö¾P©ú²Ó2021.rar (18.67 KB)

¦^´_ 1# PJChen

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim d, R%
d = Sheets("VBA").[Af2]
R = [¥_°Ï!a65536].End(3).Row
With [¥_°Ï!k3].Resize(R - 2)
    .Formula = "=K2+G3-F3-H3-I3+J3"
    '.Value = .Value 'Âର­È
End With
End Sub

TOP

¦^´_ 2# samwang

±z¦n,
´ú¸Õ¨S°ÝÃD,¥t¥~·Q½Ð±Ð
¦P¤@­ÓÀÉ®×
CÄæ"¨ÑÀ³°Ó"¤À¬°¤j & ¬ü
±q²Ä3¦C¶}©l,CÄæ<>¬ü,«h¾ã¦C§R°£
³o¬O­n³æ¿Wªºµ{¦¡....

TOP

¦^´_ 2# samwang

¥t¥~,¦A½Ð±Ð

1) ¬°¤°»òµ{¦¡,¥¦¯à¦Û°Ê§ä¨ì>=dªº¤é´Á,¶i¦æ­pºâ©O?
d = Sheets("VBA").[Af2]

2) AÄæ,­n¨úBÄ檺¦~¤ë,YYYY..M
­ì¥ýªº¼gªk¬O
xR.Offset(, -1) = Year(xR) & ".." & Month(xR)
§Ú®M¥Î²{¦b·sªºµ{¦¡,§ï¬°
.Formula = "=Format([b3], "YYYY") & ".." & Format([b3], "M")"
¦ýµLªk±a¥X§Ú­nªº­È,½Ð°Ý­n¦p¦ó§ï³o¬q¡H

TOP

¦^´_ 2# samwang

±z¦n,
§Ú±NKÄæ²MªÅ,¤ÏÂаõ¦æµ{¦¡,µo²{¥¦¤£·|§P§OBÄæ>=d
d = Sheets("VBA").[Af2]
¤u§@ªí¤é´Á¬O±q6/1¶}©l,°²³]·íd=6/19
¥¦µLªk§ä¨ìBÄæ>=dªº¤é´Á,¦A¶i¦æ­pºâ
¦Ó¬O¥Ã»·³£±qK3¶}©l­pºâ

TOP

¦^´_ 4# PJChen

1) ¬°¤°»òµ{¦¡,¥¦¯à¦Û°Ê§ä¨ì>=dªº¤é´Á,¶i¦æ­pºâ©O?
>> ¤£¦n·N«ä¡A§Ú¨Sª`·N¨ì¦³³o­Ó±ø¥ó¡A¤w§ó·s¦p¤U¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

2) AÄæ,­n¨úBÄ檺¦~¤ë,YYYY..M  
>> ¤w§ó·s¦p¤U¡AÁÂÁÂ

Sub test()
Dim Arr, d, R%
d = Sheets("VBA").[Af2]
R = [¥_°Ï!b65536].End(3).Row
With Sheets("¥_°Ï").Range("a1:k" & R)
    Arr = .Value
    For i = 3 To UBound(Arr)
        .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
        If Arr(i, 2) >= d Then
            .Cells(i, "k").Formula = "=R[-1]C+RC[-4]-RC[-5]-RC[-3]-RC[-2]+RC[-1] "
        End If
    Next
    '.Value = .Value 'Âର­È
End With
End Sub

TOP

¦^´_  samwang

±z¦n,
´ú¸Õ¨S°ÝÃD,¥t¥~·Q½Ð±Ð
¦P¤@­ÓÀÉ®×
CÄæ"¨ÑÀ³°Ó"¤À¬°¤j & ¬ü
±q²Ä3¦C¶}©l,CÄæ¬ü ...
PJChen µoªí©ó 2021-9-23 19:34


½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub §R°£¦C()
Dim Arr, i&, j%, n%
With Sheets("¥_°Ï")
    With Range(.[k3], .[a65536].End(3))
        Arr = .Value
        For i = 1 To UBound(Arr)
            If Arr(i, 3) <> "¬ü" Then GoTo 99
            n = n + 1
            For j = 1 To UBound(Arr, 2)
                Arr(n, j) = Arr(i, j)
            Next
99:     Next
        .Clear
    End With
    With Range("a3").Resize(n, UBound(Arr, 2))
        .Value = Arr
        .Borders(xlBottom).Weight = xlHairline
    End With
End With
End Sub

TOP

¦^´_  samwang

±z¦n,
´ú¸Õ¨S°ÝÃD,¥t¥~·Q½Ð±Ð
¦P¤@­ÓÀÉ®×
CÄæ"¨ÑÀ³°Ó"¤À¬°¤j & ¬ü
±q²Ä3¦C¶}©l,CÄæ¬ü ...
PJChen µoªí©ó 2021-9-23 19:34


´£¨Ñ²Ä2ºØ§R°£¦C¤è¦¡¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub §R°£¦C2()
Dim xR As Range, xU As Range
For Each xR In Range("c3:c" & [c65536].End(3).Row).Rows
    If IsError(Application.Match("¬ü", xR, 0)) Then
        If xU Is Nothing Then Set xU = xR Else Set xU = Union(xR, xU)
    End If
Next
If Not xU Is Nothing Then xU.EntireRow.Delete
End Sub

TOP

¦^´_ 6# samwang

§Ú¤§«eªº³oºØ¼gªk,­n­×§ïªº®É­Ô,´N±o¤@ª½ºâÀx¦s®æ,Åý§ÚÀY©ü@@
xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
½Ð°Ý°£¤F"RC"ªº¼gªk,ÁÙ¦³¨ä¥¦¤è¦¡¶Ü?

TOP

¦^´_ 9# PJChen

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, d, R%
d = Sheets("VBA").[Af2]
R = [¥_°Ï!b65536].End(3).Row
With Sheets("¥_°Ï").Range("a1:k" & R)
    With [a3].Resize(R - 2)
    .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
    End With
    .Value = .Value 'Âର­È
    Arr = .Value
    For i = 3 To UBound(Arr)
        If Arr(i, 2) >= d Then
            .Cells(i, "k").Formula = "=k" & i - 1 & "+G" & i & "-F" & i & "-H" & i & "-I" & i & "+j" & i
        End If
    Next
    '.Value = .Value 'Âର­È
End With
End Sub

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD