VBA返回应用程序.Sumif在变量中回答

Question

是否可以将如下编写的函数的答案存储在变量中？

'--------------------
'This is part of a somewhat bigger code so some variables has been declared again
'--------------------
Sub afletteren()
Dim vAfletteren         As Boolean
Dim OutWb               As Workbook
Dim OutFileStatus       As Range
Dim OutFileHeader       As Range
Dim Key1                As Variant
Dim Key2                As Variant
Dim OutFileKPL          As Range
Dim OutFileGBR          As Range
Dim OutFileBedrag       As Range
Dim OutFileStatistieknr As Range
Dim OutFileVouchernr    As Range
vAfletteren = True
Set OutWb = ActiveWorkbook
Set OutFileHeader = Range("D5")
If vAfletteren = True Then
With OutWb.Sheets("Dump")
Set OutFileKPL = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:="KPL", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set OutFileGBR = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:="Rekeningnummer", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set OutFileBedrag = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:="BedragPrimair", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set OutFileStatistieknr = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:="Statistieknummer1", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set OutFileVouchernr = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:="FactVerplNr", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set OutFileStatus = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:="Status", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
End With
'Whenever key1 = 0 then set the "Status" to "Afletteren", the first row of this example equals to 0 so after running this code, the outcome has to be "Afletteren" in the first cell of the status column
S = OutFileHeader.Row + 1
With OutWb.Sheets("Dump")
For T = OutFileHeader.Row + 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If .Cells(S, OutFileStatus.Column).Value <> "Afletteren" Then
Key1 = Application.SumIfs(OutFileBedrag.EntireColumn.Address, OutFileKPL.EntireColumn.Address, .Range(Split(Cells(1, OutFileKPL.Column).Address, "$")(1) & S).Value, OutFileGBR.EntireColumn.Address, .Range(Split(Cells(1, OutFileGBR.Column).Address, "$")(1) & S).Value, OutFileVouchernr.EntireColumn.Address, .Range(Split(Cells(1, OutFileVouchernr.Column).Address, "$")(1) & S).Value)
'Key1 = Application.SumIfs(sumrange                          , Range 1                        , Criteria 1                                                          , Range 2                        , Criteria 2                                                          , Range 3                              , Criteria 3

'Check
MsgBox "Application.SumIfs(" & OutFileBedrag.EntireColumn.Address & ", " & OutFileKPL.EntireColumn.Address & ", " & .Range(Split(Cells(1, OutFileKPL.Column).Address, "$")(1) & S).Value & ", " & OutFileGBR.EntireColumn.Address & ", " & .Range(Split(Cells(1, OutFileGBR.Column).Address, "$")(1) & S).Value & ", " & OutFileVouchernr.EntireColumn.Address & ", " & .Range(Split(Cells(1, OutFileVouchernr.Column).Address, "$")(1) & S).Value & ")"
MsgBox Key1         'has to be equal to 0
If Key1 = 0 Then
.Cells(S, OutFileStatus.Column).Value = "Afletteren"
End If
End If
S = S + 1
Next T
End With
End If
Exit Sub
ERROR_HANDLING:
MsgBox "Error Handling = " & vStatus
End Sub

每当我运行这段代码时，它都会在msgbox行返回一个类型错误。是否可以将函数的答案存储在变量中，而不是预先定义的范围中？每当Sumifs函数返回值0时，我想继续。

我希望能收到某人的来信。

提前感谢

Answer 1

这是可能的，这里有一个代码示例：

Sub StackOverflow()
Dim Key1 As Variant
Key1 = Application.WorksheetFunction.SumIfs(Range("B1:B6"), Range("A1:A6"), Range("A1"))
MsgBox Key1
End Sub

显然，您需要调整范围(您可以使用变量，我只是插入了固定范围(
Messagebox显示数字结果。