在单个自动过滤器中的多个字段上的条件自动过滤器

I'm creating some coding for a statement of accounts macro and I'm checking if there is such a thing as a conditional autofilter over multiple fields, ie:

我正在创建一个帐户声明宏的编码,我正在检查是否存在多个字段的条件自动过滤器这样的事情,即:

TSOA.Range.AutoFilter Field:=8, Criteria1:="Unpaid", Operator:=xlOr, Field:=9, Criteria2:=">" & Dbl3M

So I need entries that are either unpaid or within 3 months of this month (not mutually exclusive, so the only ones that are really filtered out are paid and older than 3 months ago). So any entries after the 28/02/2015, paid or unpaid, must be included (I know this sound complicated).

因此,我需要未付款的条目或本月3个月内的条目(不是互相排斥的,因此唯一真正过滤掉的条目是3个月前支付的款项)。所以在2015年2月28日之后的任何参赛作品,无论是付费的还是未付的,都必须包括在内(我知道这听起来很复杂)。

Anyway if this doesn't work, I've got some workarounds up my sleeve by autofiltering them by individual criteria, adding them both to a single array and removing duplicates! I just wanted to know the limitations of the autofilter function and whether this could actually be done. Thank you.

无论如何,如果这不起作用,我通过个别标准对它们进行自动过滤,将它们添加到单个阵列并删除重复项,从而获得了一些解决方法!我只想知道autofilter函数的局限性以及是否可以实现这一点。谢谢。

An illustration of how it works:

它是如何工作的说明:

So I arbitrarily made entries up to 14/04/2015 as "Paid", The filterbutton filters up to only 3 months of data left from last month (May-2), it also must include "unpaid" entries from older than 3 months ago, and include "paid" entries within the 3 months.

因此我随意将截至2015年4月14日的条目作为“付费”,过滤按钮过滤了上个月(5月2日)剩余的仅3个月的数据,它还必须包含3个月以上的“未付”条目之前,并在3个月内包含“付费”条目。

Here's some of the rest of the code if it helps you understand:

以下是其他一些代码,如果它可以帮助您理解:

Private Sub FilterButton2_Click()

Dim Balance As Double
Dim DblMonth As Double

With ThisWorkbook

Set TSOA = .Worksheets("SOA").ListObjects(1)
DblMonth = CLng(DateSerial(Year(Date), Month(Date), 0)) 'integer value of last month last day
Dbl3M = CLng(DateSerial(Year(Date), Month(Date) - 2, 0)) 'integer value of 3 months ago last day

If TSOA.AutoFilter.FilterMode = True Then
    TSOA.AutoFilter.ShowAllData
    TSOA.ListColumns(10).DataBodyRange.ClearContents
    Exit Sub
Else: str3 = InputBox("Please input client initials", "Client filter")
    If Application.WorksheetFunction.CountIf(.Worksheets("SOA").Range("D:D"), str3) = 0 Or str3 = "" Then
    MsgBox "Client cannot be identified!", , "Error"
    Exit Sub
    End If
End If

**ActiveSheet.AutoFilterMode = False
TSOA.Range.AutoFilter Field:=4, Criteria1:=str3
    TSOA.Range.AutoFilter Field:=8, Criteria1:="Unpaid", Operator:=xlOr, Field:=9, Criteria2:=">" & Dbl3M**

For K = 1 To TSOA.ListRows.Count

If Not TSOA.DataBodyRange.Rows(K).Hidden Then
    Balance = Balance + TSOA.DataBodyRange(K, 6).Value
    TSOA.DataBodyRange(K, 10).Value = Balance
End If
Next

End With
End Sub

1 个解决方案

#1

I think AutoFilter works on one column (Field:=1, or Field:=8, etc). It can accept multiple criteria but limited to the value in each field

我认为AutoFilter适用于一列(Field:= 1,或Field:= 8等)。它可以接受多个条件,但仅限于每个字段中的值

One other solution could be with formulas (if you can use a temporary column)

另一个解决方案可以是公式(如果你可以使用临时列)

The formula: =OR( B2="Unpaid", AND(B2="Paid", A2 < 4) )

公式:= OR(B2 =“未付”,AND(B2 =“付费”,A2 <4))

.

My Dates: 1 = This month, 2 = Last month, etc

我的日期:1 =本月,2 =上个月等