如何在SUMIF函数中使用过滤后的范围 [英] How to use filtered range into SUMIF function

问题描述

我想在SUMIF函数中使用经过过滤的范围.我的WB中有一些命名范围(Sheet1中的"uselist"是项目的一列，Sheet2中的等分"是数量的一列，Sheet2中的"dates"是日期的一列 yyyy-mm-dd ).

I want to use a filtered range into SUMIF function. I have some Named Range in my WB ("uselist" in Sheet1 is a column of item and "aliquotes" in Sheet2 is a column of quantity, "dates" in Sheet2 is a column of date yyyy-mm-dd).

我的代码中的此公式按预期方式工作(即，将使用列表"列中具有 item = cell.Value 的等分试样"中的数量相加):

This formula in my code works as expected (i.e. sum the quantities in "aliquotes" that have the item=cell.Value in "uselist" column):

Cells(cell.Row, "K") = Application.SumIf(Range("uselist"), cell.Value, Range("aliquotes")) 

但是现在我希望将总和限制为在日期"列中具有在Sheet1的"O"列中报告的日期与现在之间的日期之间的那些行.我正在工作的单元格"在Sheet1中.我修改了代码，但遇到了一些问题:

But now I want limit the sum to those rows that have in "dates" column a date between the date reported in Column "O" in Sheet1 and Now. The "cell" I'm working are in Sheet1. I modified the code, but I have some problem:

Sub FilterSum()
On Error Resume Next

Dim MyRangeI As Range
Dim MyRangeO As Range
Dim cell As Range
Dim XNEWRANGE As Range
Dim wsI As Worksheet
Dim wsO As Worksheet

Set wsI = ThisWorkbook.Sheets("Sheet1")
Set wsO = ThisWorkbook.Sheets("Sheet2")

With wsI.UsedRange 'don't consider headers
    Set MyRangeI = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1))
    MyRangeI.Select
End With

With wsO.UsedRange 'don't consider headers
    Set MyRangeO = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1))
    MyRangeO.Select
End With

Application.ScreenUpdating = False

For Each cell In MyRangeI.Columns("A").Cells

MyRangeO.AutoFilter Field:=9, Criteria1:=">=" & MyRangeI.Cells(cell.Row, "O"), Operator:=xlAnd, Criteria2:="<=" & Now 'Field 9 is tha same of the Named Range "dates"

Set XNEWRANGE = MyRangeO.SpecialCells(xlCellTypeVisible)

MyRangeI.Cells(cell.Row, "K") = Application.SumIf(XNEWRANGE, MyRangeI.cell.Value, Range("aliquotes"))

Next cell

Application.ScreenUpdating = True
End Sub

我认为要用于宏的两个不同的工作表存在问题.

In my opinion there is a problem with the two different sheets I want use for the macro.

推荐答案

尝试一下:

但是请注意您的 MyRange ，因为它没有在过程中声明:

But beware about your MyRange because it is not declared on the procedure:

Option Explicit
Sub FilterSum()

    'ActiveSheet.UsedRange.Select 'avoid using select

    Dim cell As Range
    Dim XNEWRANGE As Range
    Dim ws As Worksheet 'reference your sheets to avoid using select

    Set ws = ThisWorkbook.Sheets("MySheet") 'change MySheet for your working sheet name

    Application.ScreenUpdating = False
    With ws
        For Each cell In MyRange.Columns("A") 'MyRange isn't declared or it's value given anywhere
            'Add your filters first
            .Range("dates").AutoFilter Field:=1, Criteria1:=">=" & Cells(cell.Row, "O"), Operator:=xlAnd, Criteria2:="<=" & Now
            'Set your range using the visible cells from A2 to last row visible on column A
            Set XNEWRANGE = .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)).SpecialCells(xlCellTypeVisible)
            .Cells(cell.Row, "K") = Application.SumIf(XNEWRANGE, cell.Value, Range("aliquotes"))
        Next cell
    End With
    Application.ScreenUpdating = True

End Sub

这篇关于如何在SUMIF函数中使用过滤后的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！