按日期过滤结果，或按两个日期过滤结果 [英] Filter Results by date, or by beetwen two dates

问题描述

我有这个代码，我想要自动填充结果的两个日期过滤（例如：从01-01-2009到02-10-2010）。我该怎么办？有人有线索吗？简化了代码（当前只有更多的范围）。我试图添加在If函数中的条件，但我只收到错误...感谢您的帮助。顺便说一下，我对这个代码有很大的帮助，所以我在excel宏vba中是一个小小的东西）。

dimResult1，clean1等代表日期值。如果oCellResult1.Offset（iCellCount，0）.Value = oCell.Offset（0,4）.Value不在两个日期之间，那么我不能跳过nextcell。

  Private Sub Worksheet_Change（ByVal Target As Range）
 
 Dim oCell As Excel.Range 
 Dim oCellResult1 As Excel.Range 
 Dim oCellResult2 As Excel.Range 
 Dim oCellClean1 As Excel.Range 
 Dim oCellClean2 As Excel.Range 
 Dim oRangeID As Excel.Range 
 Dim iCellCount As Integer 
 
 
如果Target.Address =$ T $ 4然后
 
'设置源数据
设置oRangeID =表（Registo_EPI）。范围（A3： A5000）
 
'定义获取结果的初始目标
'data 
设置oCellResult1 =表（Distribuição_EPI）。范围（U12）
' luvas 
 Set oCellResult2 = Sheets（Distribuição_EPI）。范围（E12）
 
'清除任何以前的数据
设置oCellClean1 = oCellResult1 
设置oCellClean2 = oCellResult2 
虽然Len（o CellClean1.Value）> 0 
 
 oCellClean1.ClearContents 
设置oCellClean1 = oCellClean1.Offset（1，0）
 
 oCellClean2.ClearContents 
设置oCellClean2 = oCellClean2.Offset（1 ，0）
 
 Wend 
 
'扫描匹配数据的源范围
对于每个oCell在oRangeID 
 
如果oCell.Value = 然后退出
 
如果oCell.Value = Target.Value然后
 
'data 
 oCellResult1.Offset（iCellCount，0）.Value = oCell.Offset（ 0，4）.Value 
'luvas 
 oCellResult2.Offset（iCellCount，0）.Value = oCell.Offset（0,9）.Value 
 iCellCount = iCellCount + 1 
 
如果iCellCount = 14然后iCellCount = iCellCount + 20 
 
 
结束如果
 
下一个oCell 
 
结束如果
 
 End Sub 
  

解决方案

正确的你想要实现的，我们可以添加一个ne我们的IF声明中的条件。

它将是这样的：

 '确保oCell中的值是一个日期;否则比较将不起作用
如果oCell.Value = Target.Value和IsDate（oCell.Value）然后
 
'日期比较
如果cDate（oCell.Value）> ; cdate（01-01-2009）和_ 
 cDate（oCell.Value） （02-10-2010）然后
 
'data 
 oCellResult1.Offset（iCellCount，0）.Value = oCell.Offset（0，4）.Value 
' luvas 
 oCellResult2.Offset（iCellCount，0）.Value = oCell.Offset（0,9）.Value 
 iCellCount = iCellCount + 1 
 
如果iCellCount = 14然后iCellCount = iCellCount + 20 
 
如果
 
结束如果
  

I have this code and i want do filter the autofill results by two dates (ex: from 01-01-2009 to 02-10-2010). How can i do this? Does anybody have a clue? The code bellow is simplified (the current one only have more Ranges). I´ve tried to add condidions in the If Function but i only get error... thanks for your help. By the way i had a great help for this code so i´m a litte noob in excel macro vba :).

The dimResult1, clean1 etc stands for the date values. What i can´t do is to skip to nextcell if oCellResult1.Offset(iCellCount, 0).Value = oCell.Offset(0, 4).Value isn´t between the two dates.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell As Excel.Range
Dim oCellResult1 As Excel.Range
Dim oCellResult2 As Excel.Range
Dim oCellClean1 As Excel.Range
Dim oCellClean2 As Excel.Range
Dim oRangeID As Excel.Range
Dim iCellCount As Integer


If Target.Address = "$T$4" Then

    'Set source data
    Set oRangeID = Sheets("Registo_EPI").Range("A3:A5000")

    'Define initial target for the results obtained
    'data
    Set oCellResult1 = Sheets("Distribuição_EPI").Range("U12") 
    'luvas
    Set oCellResult2 = Sheets("Distribuição_EPI").Range("E12") 

    'Clear up any previous data
    Set oCellClean1 = oCellResult1
    Set oCellClean2 = oCellResult2
    While Len(oCellClean1.Value) > 0

        oCellClean1.ClearContents
        Set oCellClean1 = oCellClean1.Offset(1, 0)

        oCellClean2.ClearContents
        Set oCellClean2 = oCellClean2.Offset(1, 0)

    Wend

    'Scans source range for match data
    For Each oCell In oRangeID

        If oCell.Value = "" Then Exit For

        If oCell.Value = Target.Value Then

           'data
           oCellResult1.Offset(iCellCount, 0).Value = oCell.Offset(0, 4).Value 
           'luvas
           oCellResult2.Offset(iCellCount, 0).Value = oCell.Offset(0, 9).Value 
           iCellCount = iCellCount + 1

           If iCellCount = 14 Then iCellCount = iCellCount + 20


        End If

    Next oCell

End If

End Sub

解决方案

If I understood correctly what you're trying to achieve, we can add a new condition in our IF statement.

It would be something like:

    'Ensure the value in oCell is a date; otherwise the comparison won't work
    If oCell.Value = Target.Value and IsDate(oCell.Value) Then

        'Date Comparison
        if cDate(oCell.Value) > cdate("01-01-2009") and _
            cDate(oCell.Value) < ("02-10-2010") then

            'data
            oCellResult1.Offset(iCellCount, 0).Value = oCell.Offset(0, 4).Value 
            'luvas
            oCellResult2.Offset(iCellCount, 0).Value = oCell.Offset(0, 9).Value 
            iCellCount = iCellCount + 1

            If iCellCount = 14 Then iCellCount = iCellCount + 20

        end if

    End If

这篇关于按日期过滤结果，或按两个日期过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！