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

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

¦^´_ 39# samwang
Sam ±ß¤W¦n,
§Ú·Q¨Ìsingo1232001ªº«Øij,³]©w¤é´Á°Ï¶¡(¦³®É·|¥Î¨ì),
³]©w°Ï¶¡2021/9/18(§t)~2021/9/29(§t)
²Å¦X±ø¥ó«h§R°£<>"¬ü"
    If xR.Offset(, -1) < [AF1] And xR.Offset(, -1) < [AF2] Then GoTo 99
¦ý2021/9/29(§t)ªº³]©wµL®Ä,
§R°£ªº¬O9/18¤§«áªº¥þ³¡¤é´Á
½ÐÀ°¦£¬Ý¤Uµ{¦¡ ~~·PÁÂ~~
¾ã¦C§R°£_3­Ó±ø¥ó_°_¨´¤é´Á.rar (18.34 KB)

TOP

¦^´_ 41# PJChen

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

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

TOP

°µ­Ó¸û§¹¾ã+¨¾§bªº¤èªk:
Sub §R°£¦C()
Dim D(2) As Date, K%, MS$, xR As Range, xU As Range, N&
If IsDate([AF1]) Then D(1) = [AF1]: K = 1
If IsDate([AF2]) Then D(2) = [AF2]: K = K + 2
If K = 0 Then MsgBox "¡°¥¼«ü©w§R°£¤é´Á! ": Exit Sub
If K = 1 Then D(2) = D(1): MS = D(1) & " ¤§«áªº¸ê®Æ"
If K = 2 Then D(1) = D(2): MS = D(2) & " ¤§«eªº¸ê®Æ"
If K = 3 Then
   If D(2) < D(1) Then D(0) = D(1): D(1) = D(2): D(2) = D(0)
   MS = D(1) & " ¦Ü " & D(2) & " ¤§¶¡ªº¸ê®Æ"
   If D(1) = D(2) Then MS = D(1) & " ·í¤Ñªº¸ê®Æ"
End If
If MsgBox("¡°½T©w­n§R°£ " & MS & "¡H  ", 1 + 32 + 256) = vbCancel Then Exit Sub
'---------------------------------------------
For Each xR In Range([c3], [c65536].End(3))
    If xR = "¬ü" Or IsDate(xR(1, 0)) = False Then GoTo 99
    D(0) = xR(1, 0)
    If D(0) < D(1) Or D(0) > D(2) Then GoTo 99
    N = N + 1
    If N = 1 Then Set xU = xR Else Set xU = Union(xR, xU)
99: Next
If N = 0 Then MsgBox "¡°°õ¦æ§¹²¦! §ä¤£¨ì²Å¦Xªº¸ê®Æ!  ": Exit Sub
xU.Select
If MsgBox("¡°°õ¦æ§¹²¦! ¦@§ä¨ì " & N & " µ§²Å¦X¸ê®Æ¡A¬O§_­n§R°£¡H  ", 4 + 32 + 256) = vbYes Then xU.EntireRow.Delete
End Sub

ªþ¥ó:
§R°£¦C_3­Ó±ø¥ó_°_¨´¤é´Á-v1.rar (21.29 KB)


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

TOP

¦^´_ 43# ­ã´£³¡ªL
­ã¤j¦n,
·PÁÂ^^
°õ¦æOK

¦^´_ 42# samwang
If xR = "Ÿ" Then GoTo 99
If xR.Offset(, -1) > [AF1] And xR.Offset(, -1) < [AF2] Then GoTo 99'³o¦æªº¼gªk,·|¾É­P°õ¦æµ²ªG¿ù»~
§Ú¤@ª½´ú¸Õ¦UºØ²Õ¦X,«á¨Ó§ï¬°¥H¤U,«hµ²ªG¥¿½T
  1. For Each xR In Range("c3:c" & [c65536].End(3).Row).Rows
  2.     If xR = "¬ü" Then GoTo 99
  3.     If xR.Offset(, -1) < [AF1] Then GoTo 99
  4.     If xR.Offset(, -1) > [AF2] Then GoTo 99
  5.     Set xC = xR
  6.     If xU Is Nothing Then Set xU = xR Else Set xU = Union(xR, xU)
  7. 99: Next
  8. If Not xU Is Nothing Then xU.EntireRow.Delete
½Æ»s¥N½X

TOP

¦^´_ 44# PJChen

¤£¦n·N«ä¡A¤Ó²Ê¤ß¤j·N¨S¦³ª`·N¨ì°ÝÃD¡A¤]¥i§ï¬°¦p¤U¡AÁÂÁÂ


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

TOP

¦^´_ 36# samwang

Sam±z¦n,
§Ú±N²Î­p§ï¬°IÄæ,¤Sµo²{Ãþ¦ü°ÝÃD,
¤W¦^¬O³Ì¥½¤@¦C³æ¸¹ªÅ¥Õ®É,µLªk²Î­p¼Æ¶q
³o¦¸¬O³Ì¥½¤@¦C,µLªk²Î­p¼Æ¶q
¤¤°Ï_¦h¦~«×.rar (87.81 KB)
  1. Sub test()
  2. Dim Arr, Brr, xD, i&, T$, T1$
  3. Arr = Sheets("«n°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1)
  4. ReDim Brr(1 To UBound(Arr), 1 To 1)
  5. Set xD = CreateObject("Scripting.Dictionary")
  6. For i = 1 To UBound(Arr)
  7.     If Not IsDate(Arr(i, 1)) Then GoTo 98
  8.     T = Year(Arr(i, 1)) & "|" & Month(Arr(i, 1)): T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1))
  9.     If xD.Exists(T) Then
  10.         If T <> T1 Then
  11.             xD(Arr(i, 1)) = Val(xD(T)) + Val(Arr(i, 4))
  12.         Else
  13.             xD(T) = Val(xD(T)) + Val(Arr(i, 4))
  14.         End If
  15.     Else
  16.         xD(T) = Val(Arr(i, 4))
  17.     End If
  18. 98: Next
  19. For Each ky In xD.keys
  20.     For i = 1 To UBound(Arr)
  21.         If Not IsDate(Arr(i, 1)) Then GoTo 99
  22.         T = Year(Arr(i, 1)) & "|" & Month(Arr(i, 1)): T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1))
  23.         If T <> T1 Then Brr(i, 1) = xD(Arr(i, 1))
  24. 99: Next
  25. Next
  26. Sheets("«n°Ï").[i3].Resize(UBound(Brr)) = Brr
  27. End Sub
½Æ»s¥N½X

TOP

¦^´_ 46# PJChen

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

Sub test()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("«n°Ï").Range("a3:k" & [«n°Ï!a65536].End(3).Row)
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))
    If i < UBound(Arr) Then T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1)) Else T1 = 0
    If xD.Exists(T) Then
        If T <> T1 Then
            xD(Arr(i, 1)) = Val(xD(T)) + Val(Arr(i, 4))
            Brr(i, 1) = xD(Arr(i, 1))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4))
        End If
    Else
        xD(T) = Val(Arr(i, 4))
    End If
98: Next
Sheets("«n°Ï").[i3].Resize(UBound(Brr)) = Brr
End Sub

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD