时间戳

首页 > 解决方案 > 在列中的日期之间自动过滤 + 另一个未填充目标的条件

excel - 在列中的日期之间自动过滤 + 另一个未填充目标的条件

问题描述

我有一个数据表（名为 BI_Comissoes 的表），其中有日期列和推销员列。然后，我得到了目标表，该表用作根据在 B7 和 B8 上输入的日期范围以及在 B5 上输入的推销员姓名填充的报告。运行代码后，不会填充目标表，但会正常过滤数据表。该代码以前可以工作，但我想我在某个地方弄乱了它，我的神经元似乎被烧毁了，因为我无法找出问题所在。

我尝试了许多日期格式，但似乎没有任何效果。供您参考，数据表上的日期为 dd/mm/yyyy。

Sub SelectDataBetweenTwoDates()
    'declare variables
    Dim fromDate, toDate As Date
    Dim MyResults As Worksheet, MyData As Worksheet, MyDates As Worksheet

    Set MyResults = Worksheets("Relatório de Comissão")
    Set MyData = Worksheets("BI_Comissoes")
    Set MyDates = Worksheets("Relatório de Comissão")

    'clear previous results
    'MyResults.Cells.Clear

    'attribute date values to variables
    fromDate = MyDates.Range("B7").Value
    toDate = MyDates.Range("B8").Value
    Vendedor = MyResults.Range("B5").Value

    'convert to text format to allow filtering
    fromDate = Format(fromDate, "dd-mmm-yyyy")
    toDate = Format(toDate, "dd-mmm-yyyy")

    'clear previous filtered data
    With MyResults
    'If fromDate = "" Or toDate = "" Then
        'MsgBox "Informar o período!"
        'Exit Sub
    'Else: Resume
    On Error Resume Next
    Err.Number = 0
    .Range("A$17:$K$20000").Select
    Intersect(Selection, _
        Selection.SpecialCells(xlCellTypeConstants, 23)).ClearContents
    End With

    With MyData
    'removes autofilter
            If .FilterMode Then .ShowAllData

    'filter the data based on selected date values
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
            .Range("$B$2:$B$" & lastrow).AutoFilter Field:=2, Criteria1:= _
                ">=" & fromDate, Operator:=xlAnd, Criteria2:="<=" & toDate
            .Range("G$2:G" & lastrow).AutoFilter Field:=7, Criteria1:=Vendedor

    'copy the filtered data
            .Range("$B$2:$B$30000").SpecialCells(xlCellTypeVisible).Copy
            MyResults.Range("A17").PasteSpecial _
            Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

    End With

    'Informs the user, incase nothing has been parsed into the results table
    If MyResults.Range("A17").Value = "" Then
        MsgBox "Não há comissão neste período."
    End If

    'brings selection to the main cell
    MyResults.Activate
    MyResults.Range("B5").Select

    End Sub

目的是根据输入的日期范围和推销员填充 A17:K。

任何帮助是极大的赞赏。

标签： excelvba

解决方案

最后，这是我解决它的方法，以防有人遇到这种问题：

Sub SelectDataBetweenTwoDates()

'declare variables
    Dim fromDate, toDate As Date
    Dim MyResults As Worksheet, MyData As Worksheet, MyDates As Worksheet

    Set MyResults = Worksheets("Relatório de Comissão")
    Set MyData = Worksheets("BI_Comissoes")
    Set MyDates = Worksheets("Relatório de Comissão")

'attribute date values to variables
    fromDate = MyDates.Range("B7").Value
    toDate = MyDates.Range("B8").Value
    Vendedor = MyResults.Range("B5").Value

'**THIS** is the part I commented out, since these are declared as Date, thus compatible with the data I'm trying to filter. Then, there was no need to convert it to string for filtering purposes.    
'convert to text format to allow filtering
    'fromDate = Format(fromDate, "dd/mm/yyyy")
    'toDate = Format(toDate, "dd/mm/yyyy")
    'MsgBox fromDate

    'clear previous filtered data
   With MyResults
    If fromDate = 0 Or toDate = 0 Then
        MsgBox "Informar o período!"
        Exit Sub
    Else
    On Error Resume Next
    Err.Number = 0
    .Range("A$17:$K$20000").Select
    Intersect(Selection, _
        Selection.SpecialCells(xlCellTypeConstants, 23)).ClearContents
    End If
    End With

    With MyData
    'removes autofilter
            If .FilterMode Then .ShowAllData

    'filter the data based on selected date values
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
            .Range("$B$2:$B$" & lastrow).AutoFilter Field:=2, Criteria1:= _
                ">=" & CDbl(fromDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(toDate)
            .Range("G$2:G" & lastrow).AutoFilter Field:=7, Criteria1:=Vendedor

推荐阅读