VBA功能:多次查找同一字符串，使用偏移量生成总和，在动态范围结束时终止

Question

我正在尝试编写一个函数，该函数根据调用函数的位置动态生成范围。 然后，它应该从该范围的顶部迭代，查找与调用函数的行开头的字符串匹配的字符串。 当字符串匹配时，它将在总和上添加两列的值。 一旦它迭代了范围，它应该终止。

在这里，我有两个几乎有效的解决方案。 两者都在执行求和的代码上失败，但否则绝对可以正常工作。 我已经注释掉了每个失败的地方。

Function Average_Power()
Dim rngSearch As Range, rngLast As Range, rngFound As Range, cell As Range
Dim CallerAddr As String, strFirstAddress As String, strFamilyName As String, teststring As String
Dim Sum As Double
Sum = 0
teststring = ""
With Application.Caller
CallerAddr = .Address
End With
strFamilyName = Application.Caller.Offset(0, -3).Value
Set rngSearch = ActiveSheet.Range("B15", Range(CallerAddr))
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
Set rngFound = rngSearch.Find(What:=strFamilyName, After:=rngLast, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
For Each cell In rngSearch.Cells
'        If InStr(cell.Value, strFamilyName) Then
'            Sum = Sum + cell.Offset(0, 2).Value
'        End If
Next cell
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Sum = Sum + rngFound.Offset(0, 2).Value
Do
'            Set rngFound = rngSearch.FindNext(rngFound)
'            Sum = Sum + rngFound.Offset(0, 2).Value
Loop Until rngFound.Address = strFirstAddress
End If
Average_Power = Sum
End Function

这是我目前的输出，因为我仍在尝试对值求和，我正在寻找最终的平均功率为 6000(4000 和 2000 的总和)：

https://i.stack.imgur.com/CMHEz.png

错误："有一个或多个循环引用，其中公式直接或间接引用其自己的单元格。 这可能会导致他们计算错误。 尝试删除或更改这些引用，或将公式移动到其他单元格"我想解决方案可能是是否有办法将范围向左移动一列，以便不迭代函数？(这仍然包括进行计算所需的所有单元格)

Answer 1

我似乎.FindNext在UDF的上下文中不可用。这并不奇怪;因为.FindNext需要依靠记忆和更新工作表上的一些"状态"，并且不允许UDF更改状态。

但是，使用另一种.Find来解决它似乎可以解决问题。此外，我注意到您迭代比赛的方式不准确。您的代码可以像这样重写：

Function Average_Power() As Double
Dim rngSearch As Range, rngFound As Range
Dim strFirstAddress As String, strFamilyName As String
strFamilyName = Application.caller.Offset(0, -3).Value
Set rngSearch = Application.caller.Parent.Range("B15", Application.caller)
Set rngFound = rngSearch.Find(What:=strFamilyName, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then Exit Function
strFirstAddress = rngFound.Address
Do
Average_Power = Average_Power + rngFound.Offset(0, 2).Value
' FindNext re-written like this will work:
Set rngFound = rngSearch.Find(What:=strFamilyName, After:=rngFound, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
''''''''''''                                      ^^^^^^^^^^^^^^^^
Loop Until rngFound.Address = strFirstAddress
End Function