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

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

¦^´_ 30# samwang
±z¦n,
µ{¦¡§ó§ï«á,¤´·|¦³¿ù»~,§Ú±N¥¦¿z¿ï¥X¨Ó,
¦A³Â·ÐÀ°¦£¬Ý¤U¡I
·PÁÂ
¤¤°Ï_¦h¦~«×new.rar (74.34 KB)

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-10-6 13:59 ½s¿è

¦^´_ 32# samwang
ÁÂÁ¡A§Ú¦A¸Õ¸Õ

TOP

¦^´_ 32# samwang

±z¦n,

¸g´ú¸Õ,³Ì¥½¤@¦C³æ¸¹ªÅ¥Õ®É,µLªk²Î­p¼Æ¶q
ªþ¤W´ú¸Õµ²ªG ¤¤°Ï_¦h¦~«×.rar (74.6 KB)

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-10-6 22:20 ½s¿è

¦^´_ 32# samwang

¸É¥R ¸ê®Æªº³W«h:

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Ãö

§Ú±Nµ{¦¡§ï¬°¥H¤U,¥i§_À°§Ú¬Ý¤U,³o¼Ë·|¦³bug¶Ü?
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 i < UBound(Arr) Then
            If Arr(i, 1) = Arr(i + 1, 1) Then GoTo 99
        End If
        Brr(i, 1) = xD(Arr(i, 1))
99: Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr
End Sub

TOP

¦^´_ 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

TOP

¦^´_ 8# samwang

Sam
½Ð°Ý¥H¤Uµ{¦¡,§Ú»Ý­n§ï¬°,²Å¦X2­Ó±ø¥ó,«h¾ã¦C§R°£,½Ð°Ý­n«ç»ò­×§ï?
1) BÄæ>=[af1]
2) CÄæ<>"¬ü"
¾ã¦C§R°£.rar (25.66 KB)
  1. Sub §R°£¦C2()
  2. Dim xR As Range, xU As Range
  3. For Each xR In Range("c3:c" & [c65536].End(3).Row).Rows
  4.     If IsError(Application.Match("¬ü", xR, 0)) Then
  5.         If xU Is Nothing Then Set xU = xR Else Set xU = Union(xR, xU)
  6.     End If
  7. Next
  8. If Not xU Is Nothing Then xU.EntireRow.Delete
  9. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-11-3 03:09 ½s¿è

¦^´_ 37# PJChen


    Sub §Rrows()
f = Columns("A:G").Find("*", , -4163, , 1, 2).Row
dt = [AF1]
Dim x1 As Range, x As Range
    For i = 3 To f
    If DateValue(Cells(i, 2)) >= DateValue(dt) And Cells(i, 3) <> "¬ü" Then
       Set x = Rows(i)
       If x1 Is Nothing Then Set x1 = x
       If Not x1 Is Nothing Then Set x1 = Union(x1, x)
    End If
    Next
    If Not x1 Is Nothing Then x1.Delete
End Sub


¥t¥~¸É¥R¤§«e´£¨ìªº°ÝÃD«Øij   ¥iÅý¹Lµ{»Pµ{¦¡°®²b²¼ä
1.«Øij¥Î¨â­Ó¤é´Á§PÂ_°_©l»Pµ²§ô ·í«e¥u¦³¤@­Ó
2.­ì©lÀɮפ£«Øij°µ¥ô¦ó­×§ï «Øijvba²£¥Íªº©Ò¦³¨BÆJ(·s­×§R§ï) ±N¸ê®Æ½Æ»s³B²z ©ñ¸m¨ì¤@­Ó·s¼Wªº¤u§@ªí°µ³B²z(¦Ü¤Ö¦³­ì©lÀÉ®× ÁקK¾Þ§@¿ù»~µLªk­Ë¦^¸ê®Æªì©lª¬ºA,¦Ó¥BÁÙ¥i¥H°µ«e«á¤ñ¹ï ®Ä²v´£¤É)
3.­ì©l¸ê®Æ(¥_°Ï)¤£«Øij©ñ¤½¦¡ ¦ý·s¼Wªº¤u§@ªí¥i¥H©ñ
VBA¤]·|¦n¼g«Ü¦h

TOP

¦^´_ 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

TOP

¦^´_ 39# samwang

¦^´_ 38# singo1232001

·PÁ¤G¦ì
µ{¦¡³£¥i¥H°õ¦æ

TOP

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