如何在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屋!