如何将 WorksheetFunction.Average 应用于 VBA 中的动态数组

Question

我在将WorksheetFunction.Average应用于VBA中的动态数组时遇到问题。数组的元素是范围，我希望能够计算动态数组中整个范围的平均值。我希望这是有道理的。（即 =常规编辑栏中的平均（D14：F15，E17：G17）。我做了大量的研究，找不到我的问题的任何答案。

With sht2.Range("B:B") 'week ending column
            Set begin_upc = .Find(upc, LookIn:=xlValues) 'find the first occurence of the upc
            Set end_upc = .Find(upc, LookIn:=xlValues, SearchDirection:=xlPrevious)   'find the last occurence of the upc
            upcRange = "A" & Range(begin_upc.Address).Row & ":" & "A" & Range(end_upc.Address).Row 'range for UPCs
            With sht2.Range(upcRange)
                For r = LBound(starts) To UBound(starts)
                    Set begin_date = .Find(starts(r), LookIn:=xlValues) 'starts is an array of start dates for the .find within the UPC range
                    Set end_date = .Find(stops(r), LookIn:=xlValues, SearchDirection:=xlPrevious) 'stops is an array for the .find within the UPC range
                    rangeToAvg = "O" & Range(begin_date.Address).Row & ":" & "O" & Range(end_date.Address).Row 'format the range
                    ReDim Preserve rangesForAvg(r) 'resize the array
                    rangesForAvg(r) = rangeToAvg 'append the range
                Next r
                avg_during = WorksheetFunction.Average(sht2.Range(rangesForAvg)) 'this is the line I need help with

Answer 1

像这样：

Dim upcRange As Range, rngData As Range, rng As Range
With sht2.Range("B:B") 'week ending column
    Set begin_upc = .Find(upc, LookIn:=xlValues)
    Set end_upc = .Find(upc, LookIn:=xlValues, SearchDirection:=xlPrevious)
    Set upcRange = sht2.Range(begin_upc, end_upc).Offset(0, -1) 'range for UPCs
    With upcRange
        Set rngData = Nothing
        For r = LBound(starts) To UBound(starts)
            Set begin_date = .Find(starts(r), LookIn:=xlValues)
            Set end_date = .Find(stops(r), LookIn:=xlValues, SearchDirection:=xlPrevious)
            Set rng = sht2.Range("O" & begin_date.Row & ":O" & end_date.Row)
            If rngData Is Nothing Then
                Set rngData = rng
            Else
                Set rngData = Application.Union(rngData, rng)
            End If
        Next r
        avg_during = WorksheetFunction.Average(rngData)