具有多个字符串条件的VBA中的SUMIFS [英] SUMIFS in VBA With Multiple String Criteria
问题描述
当我尝试运行代码时,它正在破解:
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0,i + 3),yearRange.Value,=& gEvent.Value&,yearRange.Offset(0,2),= & gEvent.Offset(0,2).Value&)
而且错误我收到的是:
运行时错误'424':
需要对象
这是完整形式的子代:
Sub CombineData()
Dim eventSheet As Worksheet
Dim yearSheet As Worksheet
Dim yearRange As Range
Dim eventRange As Range
Dim gYear As Range
Dim gEvent As Range
Dim i As Integer
Dim itemCount As Integer
Dim itemPY As整数
设置eventSheet = ThisWorkbook.Sheets(previousEvent)
设置yearSheet = ThisWorkbook.Sheets(previous12)
设置eventRange = eventSheet.Range(A3 eventSheet.Range(A3)。End(xlDown))
设置yearRange = yearSh eet.Range(A4,yearSheet.Range(A4)。End(xlDown))
对于每个gEvent在eventRange
i = 0
gEvent.Offset(0,16).Value = gEvent.Value
gEvent.Offset(0,17).Value = gEvent.Offset(0,1).Value
gEvent.Offset(0,18).Value = gEvent.Offset(0,2).Value
itemCount = Application.WorksheetFunction.CountIf(yearRange,gEvent.Value)
对于i = 0到11
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0,i + 3),yearRange.Value,=& gEvent.Value& 0,0,0,0 i).Value = itemPY / Item
下一个i
下一个gEvent
End Sub
我可以得到的任何帮助都会很棒。
不需要所有的引用:
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3),_
yearRange,gEvent.Value,_
yearRange.Offset(0,2),gEvent.Offset(0,2).Value)
I have a Sub I am writing that has a SUMIFS statement included in it. I am wanting the sum if to sum a certain column (yearRange.Offset(0,i+3)) when yearRange matches gEvent Value and yearRange.Offset(0, 2) matches gEvent.Offset(0,2) Value. I have tried googling the issue, and checked the MSDN website (which was not very descriptive) and cannot get the function to work. The only possible idea I have is that criteria I am using are Strings rather than Integers. So gEvent.Value may be "ABCD" and gEvent.Offset(0,2) may be "DEFG".
When I try to run the code it is breaking here:
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3), yearRange.Value, "=" & gEvent.Value & """, yearRange.Offset(0, 2), " = " & gEvent.Offset(0, 2).Value & """)
And the error I am receiving is: Run-time error '424': Object required
Here is the sub in it's complete form:
Sub CombineData()
Dim eventSheet As Worksheet
Dim yearSheet As Worksheet
Dim yearRange As Range
Dim eventRange As Range
Dim gYear As Range
Dim gEvent As Range
Dim i As Integer
Dim itemCount As Integer
Dim itemPY As Integer
Set eventSheet = ThisWorkbook.Sheets("previousEvent")
Set yearSheet = ThisWorkbook.Sheets("previous12")
Set eventRange = eventSheet.Range("A3", eventSheet.Range("A3").End(xlDown))
Set yearRange = yearSheet.Range("A4", yearSheet.Range("A4").End(xlDown))
For Each gEvent In eventRange
i = 0
gEvent.Offset(0, 16).Value = gEvent.Value
gEvent.Offset(0, 17).Value = gEvent.Offset(0, 1).Value
gEvent.Offset(0, 18).Value = gEvent.Offset(0, 2).Value
itemCount = Application.WorksheetFunction.CountIf(yearRange, gEvent.Value)
For i = 0 To 11
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3), yearRange.Value, "=" & gEvent.Value & """, yearRange.Offset(0, 2), " = " & gEvent.Offset(0, 2).Value & """)
gEvent.Offset(0, 19 + i).Value = itemPY / Item
Next i
Next gEvent
End Sub
Any help I could get would be great.
No need for all that quoting:
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3), _
yearRange, gEvent.Value, _
yearRange.Offset(0, 2), gEvent.Offset(0, 2).Value)
这篇关于具有多个字符串条件的VBA中的SUMIFS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!