使用单元格值作为日期范围自动过滤器 [英] Using cell values as a date range autofilter

问题描述

我正在尝试使用从2个单元格提取的范围来调整日期自动过滤器.

I'm trying to adjust my date autofilter with a range extracted from 2 cells.

经过几次失败的尝试后，我决定使用宏生成器来创建宏，以便对其进行调整以适应我的代码，最后我得到了:

After some failed tries, I decided to do the macro with the macro builder so I could tweak it to adust to my code, and I ended up with:

Dim fI As Range
Dim fF As Range

Set fI = Worksheets("Sheet1").Range("B26")

Set fF = Worksheets("Control").Range("B27")


    ActiveSheet.ListObjects("TABLE_REPORTS_GENERAL_INFO").Range _
        .AutoFilter Field:=24, Criteria1:=fI.Value, Operator:=xlAnd, _
        Criteria2:=fF.Value

日期采用正确的格式，但这只会使过滤器清洁.对日期进行硬编码有效，我在做什么错了?

The dates are in the correct format, but it will just let the filter clean. Hardcoding the dates is working, what am I doing wrong?

我的日期结构:

Starting Date   01/08/2015
Last Date   31/07/2016

推荐答案

我注意到您使用的是DMY日期格式.我会尽量保持该地区标准.

I've noticed that you are in a DMY date format. I will try to keep to that regional standard.

使用数据类型为Criteria2的数组.

Use an array with data types as Criteria2.

With ActiveSheet
    With .ListObjects("TABLE_REPORTS_GENERAL_INFO")
        .Range.AutoFilter Field:=13, Operator:=xlFilterValues, _
            Criteria2:=Array(2, Format(fi.Value, "d/m/yyyy"), 2, Format(ff.Value, "d/m/yyyy"))
    End With
End With

这应该过滤两个日期.但是，如果希望所有日期都在开始日期和结束日期之间，则需要引入运算符.

That should filter for the two dates. However, if you want all the dates between a start date and an end date then you need to introduce operators.

With ActiveSheet
    With .ListObjects("TABLE_REPORTS_GENERAL_INFO")
        .Range.AutoFilter Field:=13, _
            Criteria1:=Format(fi.Value, "\>\=d/m/yyyy"), Operator:=xlAnd, _
            Criteria2:=Format(ff.Value, "\<\=d/m/yyyy")
    End With
End With

这篇关于使用单元格值作为日期范围自动过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！