samwang

¦^´_ 19# PJChen

¥t¥~¤@­ÓUÄæµ{¦¡,¬õ¦r³o¤@¬q,¤é´Á+1ªº¼gªk¤£¯à¥Î,½Ð°Ý¦p¦óÅý¤é´Á+1
        If Arr(i, 2) >= d And Arr(i, 4) <> "¤¤©M" Or Arr(i, 4) <> "¤º´ò" And Arr(i, 4) <> "¦Á¤î" Then 'i=¦C
            .Cells(i, "u").Formula = "=B" & i & " +1 "
         ElseIf Arr(i, 2) >= d And Arr(i, 4) = "¤¤©M" Or Arr(i, 4) = "¤º´ò" And Arr(i, 4) = "¦Á¤î" Then
             .Cells(i, "u").Formula = "=B" & i & ""
         End If

«á¾Ç¤]¬O²Ä¤@¦¸¥Î³oºØ¼gªk(VBA¦bexcel¼g¤½¦¡)¡A
¼g¤F³o´X¦¸¤U¨Ó¦n¹³¦³ÂI³W«h¡A¥u­n¹J¨ìÅܼÆi    " &   i   & " µM«á & ¨ä¥¦

samwang

¦^´_ 22# PJChen

¦ý¥u­n°õ¦æ¤F .Value = .Value
´N·|¤zÂZ¨ì¨ä¥LÄæ¦ì,¤]³£Åܦ¨­È,
>> §ï¬°³æ¿WÄæÂର­È
Range("K3:K" & R).Value = Range("K3:K" & R).Value    'KÄæÂର­È

samwang

¦^´_ 22# PJChen

¥t¥~,­ì¨Ó¬O¥Î¨ç¼Æ­pºâ ¬£ªO-¥æªO®t²§,½Ð°Ý³o­Ó¦³辧ªk¶}¬°µ{¦¡¶Ü? VÄ檺µ{¦¡¤]¬O­n³æ¿W¤@­Ó®@...
V3=IF(COUNTIFS(¥_°Ï!$B$2:$B3,¥_°Ï!$B3,¥_°Ï!$D$2:$D3,¥_°Ï!$D3)=1,SUMIFS(¥_°Ï!$W:$W,¥_°Ï!$B:$B,¥_°Ï!$B3,¥_°Ï!$D:$D,¥_°Ï!$D3)-SUMIFS(¥_°Ï!$F:$F,¥_°Ï!$B:$B,¥_°Ï!$U3,¥_°Ï!$D:$D,¥_°Ï!$D3),0)
>> ³o¬O­n°µ¤°»ò??¤£ª¾¹D±z¹ê»Ú»Ý¨D¬O¤°»ò?  

samwang

¦^´_ 25# PJChen

HÄæ­n²Î­pDÄæ¨C­Ó¤ë³Ì«á¤@­Ó¤é´Áªº¤ë²Î­p¼Æ¶q
>> ¦p¤U¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("¤¤°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1)
ReDim Brr(1 To UBound(Arr), 1 To 1)
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 98
    T = Month(Arr(i, 1)): T1 = Month(Arr(i + 1, 1))
    If xD.Exists(T) Then
        If T <> T1 Then
            xD(Arr(i, 1) & "_" & Arr(i, 3)) = Val(xD(T)) + Val(Arr(i, 4))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4))
        End If
    Else
        xD(T) = Val(Arr(i, 4))
    End If
98: Next
For Each ky In xD.keys
    For i = 1 To UBound(Arr)
        Brr(i, 1) = xD(Arr(i, 1) & "_" & Arr(i, 3))
    Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr
End Sub

samwang

¦^´_ 27# PJChen

¯à§_À°§Úµù¸Ñ¤@¤U¬õ¦r¥Îªk? >>µù¸Ñ¼g±o¤£¦n¡Bµü¤£¹F·N¡A½Ð¨£½Ì¡AÁÂÁÂ

Sub test()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("¤¤°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1) '¸ê®Æ¸Ë¤J°}¦C
ReDim Brr(1 To UBound(Arr), 1 To 1) '³ÐªÅªº°}¦C¥Î¨Ó¸Ëµª®×
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 98   '¤£¬O¤é´ÁÂ÷¶}
    T = Month(Arr(i, 1)): T1 = Month(Arr(i + 1, 1))
    If xD.Exists(T) Then    '¬O§_AÄæ¤ë¥÷¦³¦b¦r¨å
        If T <> T1 Then '¦Û¤v»P¤U¤@¦C¤é´Áªº¤ë¥÷¤ñ¸û
            '¬ö¿ý·í¤ë³Ì«á¤@µ§ªº¥æªO¼Æ¶q²Ö¥[¡Akey:³Ì«á¤é´Á_±ÄÁʳ渹¡AÁקK³Ì«á¤@¤Ñ¦³¦hµ§
            xD(Arr(i, 1) & "_" & Arr(i, 3)) = Val(xD(T)) + Val(Arr(i, 4))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4)) '¦P¤ë¥÷¥æªO¼Æ¶q²Ö¥[
        End If
    Else
        xD(T) = Val(Arr(i, 4)) '¥æªO¼Æ¶q¸Ë¤J¦r¨å
    End If
98: Next
For Each ky In xD.keys  '¦r¨åkeys´`Àô
    For i = 1 To UBound(Arr)
        Brr(i, 1) = xD(Arr(i, 1) & "_" & Arr(i, 3)) '¦r¨åkey:³Ì«á¤é´Á_±ÄÁʳ渹¡A¸Ë¤Jµª®×°}¦C
    Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr  'Åã¥Üµª®×
End Sub

samwang

¦^´_ 29# PJChen


¸ê®Æ¦³¦h¦~«×®É,¥u¦³²Ä¤@¦~­pºâ¥¿½T,¨ä¾l¦X­p·|¥X¿ù...   
>> §ó§ï¦p¤U¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
T = Year(Arr(i, 1)) & "|" & Month(Arr(i, 1)): T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1))

samwang

¦^´_ 31# PJChen

¦h¾lªº²Î­p°ÝÃD
>> §ó·s¦p¤U¬õ¦r¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
For Each ky In xD.keys
    For i = 1 To UBound(Arr)
        If i < UBound(Arr) Then
            If Arr(i, 1) & "_" & Arr(i, 3) = Arr(i, 1) & "_" & Arr(i + 1, 3) Then GoTo 99
        End If
        Brr(i, 1) = xD(Arr(i, 1) & "_" & Arr(i, 3))
99: Next
Next

samwang

¦^´_ 35# PJChen

1) AÄæ¨C¤ë²Ä¤@µ§¤é´Á,¤£¤@©w1¤é¶}©l
2) AÄæ¨C¤ë³Ì«á¤@µ§¤]¤£¤@©w¬O¤ë©³¤é,¥u­n¬O¨C¤ëªº³Ì«á¥X²{ªº¤é´Á´N¶i¦æ²Î­p
3) ­n§â¦~«×¤]¦Ò¶q¶i¥h,¦]¬°¸ê®Æ·|¤£Â_¼W¥[,¤£·|¥u¦³¤@­Ó¦~«×
4) CÄ檺³æ¸¹,·|¦³¦P¤@¤é´Á,¦P¤@³æ¸¹­«½Æ¼Æ¦¸,§Ú·Qµ{¦¡¤¤À³¸Ó¤£¥Î¥[¤WCÄ檺§P§O,¦]¬°³æ¸¹»P²Î­pµLÃö
>> ±z¯uªº«Ü¥Î¤ß§â©Ò¦³³W«h±ø¥ó¦C¥X¡A35#¦³¤p°ÝÃD§Ú­×§ï«á¦p¤U¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test2()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("¤¤°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1)
ReDim Brr(1 To UBound(Arr), 1 To 1)
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 98
    T = Year(Arr(i, 1)) & "|" & Month(Arr(i, 1)): T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1))
    If xD.Exists(T) Then
        If T <> T1 Then
            xD(Arr(i, 1)) = Val(xD(T)) + Val(Arr(i, 4))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4))
        End If
    Else
        xD(T) = Val(Arr(i, 4))
    End If
98: Next
For Each ky In xD.keys
    For i = 1 To UBound(Arr)
        If Not IsDate(Arr(i, 1)) Then GoTo 99
        T = Year(Arr(i, 1)) & "|" & Month(Arr(i, 1)): T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1))
        If T <> T1 Then Brr(i, 1) = xD(Arr(i, 1))
99: Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr
End Sub

samwang

¦^´_ 37# PJChen


Sub §R°£¦C()
Dim xR As Range, xU As Range
For Each xR In Range("c3:c" & [c65536].End(3).Row).Rows
    If xR = "¬ü" Then GoTo 99
    If xR.Offset(, -1) < [AF1] Then GoTo 99
    Set xC = xR
    If xU Is Nothing Then Set xU = xR Else Set xU = Union(xR, xU)
99: Next
If Not xU Is Nothing Then xU.EntireRow.Delete
End Sub

samwang

¦^´_ 41# PJChen

³]©w°Ï¶¡2021/9/18(§t)~2021/9/29(§t)

If xR.Offset(, -1) > [AF1] And xR.Offset(, -1) < [AF2] Then GoTo 99