excel - VBA SumIfs 范围对象

Question

我正在尝试构建一个宏来创建一个基于 SumIfs 公式的表，其中包含 2 个条件。行是标准 1，列是标准 2。 SumIfs 的结果是表格。我询问用户标准 1、标准 2 的范围和工作表中的总和 (StrSheetName)，然后在另一张工作表中构建结果。

不起作用的部分，我相信它与范围对象有关：

Cells(cellposY, cellPosX).Value = wf.SumIfs(sumRange, Criteria1, Worksheets("SumIfTable").Cells(cellposY, 2).Value, Criteria2, Worksheets("SumIfTable").Cells(2, cellPosX).Value)

编辑：

那是我的测试表（输入数据）：

这就是我想要做的：

完整代码：

Dim InputRangeCritY As Range
Dim InputRangeCritX As Range
Dim InputSumValue As Range

Dim temp As Variant

Set InputRangeCritY = Application.InputBox(prompt:="Select first criteria/column, would be displayed in Y :", Type:=8)
Set InputRangeCritX = Application.InputBox(prompt:="Select second criteria/column, would be displayed in X :", Type:=8)
Set InputSumValue = Application.InputBox(prompt:="Select sum valued, will be use for the sumif formula :", Type:=8)

InputRangeCritY.Select
Selection.Copy

Dim StrSheetName As String
StrSheetName = ActiveSheet.Name

Sheets.Add.Name = "SumIfTable"
Worksheets("SumIfTable").Activate
Range("B3").Select
ActiveSheet.Paste
Range("B3").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlNo

Worksheets(StrSheetName).Activate
InputRangeCritX.Select
Selection.Copy

Worksheets("SumIfTable").Activate
Range("D3").Select
ActiveSheet.Paste
Range("D3").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlNo
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Dim OutputRangeCritY As Range
Dim OutputRangeCritX As Range

Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Set OutputRangeCritY = Selection

Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Set OutputRangeCritX = Selection

Dim cellPosX As Long
Dim cellposY As Long
cellPosX = 3
cellposY = 3

Set sumRange = Worksheets(StrSheetName).Range(InputSumValue.Address)
Set Criteria1 = Worksheets(StrSheetName).Range(InputRangeCritY.Address)
Set Criteria2 = Worksheets(StrSheetName).Range(InputRangeCritX.Address)
Dim wf As WorksheetFunction

Dim i As Long
Dim J As Long
J = OutputRangeCritY.Cells.Count

Dim g As Long
Dim H As Long
H = OutputRangeCritX.Cells.Count

For g = 1 To H
    For i = 1 To J
        Cells(cellposY, cellPosX).Value = wf.SumIfs(sumRange, Criteria1, Worksheets("SumIfTable").Cells(cellposY, 2).Value, Criteria2, Worksheets("SumIfTable").Cells(2, cellPosX).Value)
        cellposY = cellposY + 1
    Next
    cellPosX = cellPosX + 1
Next

标签： excelvbarangesumifs