[µo°Ý] ¨Ì¤é´Á¨ú¸ê®Æ

qaqa3296

¨Ì¤À­¶1ªºE2¤é´Á¥h§PÂ_¤À­¶¥Ø¼Ðªº¥Í®Ä»P¥¢®Ä¤é´Á

¤U¸ü (82.29 KB)

2020-9-12 18:16

±ø¥ó
«ö¶s©ñ¨ì¤À­¶1
¥Í®Ä»P¥¢®Ä¤é´Á¬°ªÅ¥Õ®É¡A¸Ó¦æ­n¦C¥X
¥Í®Ä¤é´Á»P¥¢®Ä¤é´Á³£¬Û¦P®É¡A¤w¥¢®Ä¬°·Ç¡A¸Ó¦æ¤£¦C¥X

²Å¦X±ø¥óªº¸ê®Æ¦C¨ì¦¨ªG¡A¥H2020/09/12¬°¨Ò

¤U¸ü (32.71 KB)

2020-9-12 18:16

¤é´Á¤ñ¹ï­n¦p¦ó¼g¨S¦³ÀYºü¡Aª¦¤å¬ÝCDdate¡BdateVaule¡B#()#¡A¬Ý¤£À´¦p¦ó¹B¥Î¡A§Æ±æ¦U¦ì¤j¤jÀ°¦£

­ã´£³¡ªL

¦pªG¸ê®Æ¤£¦h, ª½±µ¥Îrange§Y¥i
Sub TEST_A3()
Dim xR As Range, D
[¦¨ªG!A2:D2000].ClearContents
D = [Form!E2]: If D = "" Then Exit Sub
Application.ScreenUpdating = False
For Each xR In Range([¥Ø¼Ð!A2], [¥Ø¼Ð!A65536].End(xlUp))
    If (xR(1, 5) > D) + (xR(1, 6) <> "") * (xR(1, 6) <= D) Then GoTo 101
    xR.Resize(1, 4).Copy [¦¨ªG!A65536].End(xlUp)(2)
101: Next
End Sub

­ã´£³¡ªL

¦pªG¤£§PÂ_¤é´Á®æ¦¡:
Sub TEST_A2()
Dim Arr, D, i&, j%, N&
[¦¨ªG!A2:D2000].ClearContents
D = [Form!E2]: If D = "" Then Exit Sub
Arr = Range([¥Ø¼Ð!F1], [¥Ø¼Ð!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    If Arr(i, 5) <> "" And Arr(i, 5) > D Then GoTo i01
    If Arr(i, 6) <> "" And Arr(i, 6) <= D Then GoTo i01
    N = N + 1
    For j = 1 To 4: Arr(N + 1, j) = Arr(i, j): Next j
i01: Next i
If N > 0 Then [¦¨ªG!A1].Resize(N + 1, 4) = Arr
End Sub

jcchiang

¦^´_ 14# qaqa3296


    §Ú¥u¬O¨Ì¾Ú§AªºÀɮפº®e¥h¼g,¨Ã¨S¦³¥h§PÂ_¥i¯àªº¤é´Á®æ¦¡°ÝÃD

°a¤ªºµ

­ã¤j¤j ÁÙ¦³ jcchiang¤j¤j ªº «ä¦ÒÅÞ¿è§ó¾ã±K   
¥i¥HÂл\¨Ï¥Î­ì¨Óªº°}¦CªÅ¶¡ ÁÙ¦³²¤¹Lªº¤è¦¡°µ§PÂ_
    ¾Ç²ß¤¤

°a¤ªºµ

¬Ý°_¨Ó¦n¹³¬O¥þ³£¨Ì¾Úª«¥ó©Î½d³ò¥»¨­ªº®æ¦¡

qaqa3296

¦^´_ 13# jcchiang

·PÁ­㴣³¡ªL¡Bjcchiang

´£¨Ñ¨ä¥L¼gªk¨Ñ¤j®a¾Ç²ß

µ{¦¡®ÄªG³£¥¿½T

·Q°Ý¤@¤Ujcchiang¤j¤j

§Aªºµ{¦¡§Ú¬Ý¤£¥X¨Ó¦³«Å§i©Î¨ä¥L¤é´Á¨ç¼Æ?

¨º¬°¦ó¥i¥H¥¿½T§PÂ_¤é´Á©O?

ÁÙ¬OÂæb­þ§Ú¨S¦³ª`·N¨ì?

jcchiang

¦^´_ 7# qaqa3296

¸Õ¸Õ¬Ý
Sub ex()
Dim c As Range
Dim a As Object
Dim ¤é´Á$
¤é´Á = Sheets("Form").[E2]
Sheets("¦¨ªG").Range([a2], [a2].End(4).Offset(, 5)).ClearContents
Set c = Nothing
With Sheets("¥Ø¼Ð")
For Each a In .Range(.[a2], .[a65535].End(3))
   If (a.Offset(, 5) = "" Or a.Offset(, 5) > ¤é´Á) And (a.Offset(, 4) = "" Or a.Offset(, 4) <= ¤é´Á) Then
      If c Is Nothing Then
         Set c = a.Resize(, 6)
      Else
         Set c = Union(c, a.Resize(, 6))
      End If
   End If
Next
End With
c.Copy Sheets("¦¨ªG").[a2]
Set c = Nothing
End Sub

­ã´£³¡ªL

Sub TEST_A1()
Dim Arr, T$(2), D(2), i&, j%, N&
[¦¨ªG!A2:D2000].ClearContents
D(0) = [Form!E2]
If IsDate(D(0)) = False Then Exit Sub
Arr = Range([¥Ø¼Ð!F1], [¥Ø¼Ð!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    If Arr(i, 1) = "" Then GoTo i01 '[«~¸¹]ªÅ¥Õ, ²¤¹L
    D(1) = Arr(i, 5) '[¥Í®Ä¤é´Á]
    D(2) = Arr(i, 6) '[¥¢®Ä¤é´Á]
    If IsDate(D(1)) Then If CDate(D(1)) > D(0) Then GoTo i01 '[¥Í®Ä¤é´Á]¦³¤é´Á, ¥B>«ü©w¤é, ²¤¹L
    If IsDate(D(2)) Then If CDate(D(2)) <= D(0) Then GoTo i01 '[¥¢®Ä¤é´Á]¦³¤é´Á, ¥B<=«ü©w¤é, ²¤¹L
    N = N + 1
    For j = 1 To 4: Arr(N + 1, j) = Arr(i, j): Next j
i01: Next i
If N > 0 Then [¦¨ªG!A1].Resize(N + 1, 4) = Arr
End Sub

¨â­Ó¤é´Á¬Û¦P, ¥²¥]§t¦b¨º¨â­Óif±ø¥ó¤¤, ¤£¥²¥t¦æ§PÂ_:
¨Ò¦p:«ü©w¤é2020/9/8
2020/9/7  2020/9/7 ¥¢®Ä¤é´Á<=«ü©w¤é
2020/9/12  2020/9/12  ¥Í®Ä¤é´Á>«ü©w¤é


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

°a¤ªºµ

¦^´_ 10# qaqa3296
§O³o»ò»¡  §Ú¤]¬O·s¤â §Ú­Ì¤¬¬Û¾Ç²ß ¤¬¬ÛÀ°§U   §Ú·QµLªk¸Ñ¨Mªº°ÝÃD  ¦b´£¥X¨Ó¤j¤j­Ì¤]·|¦³µª®×