WorksheetFunction SumProduct Error 1004 in nested loop

Question

I'm running into a '1004' error when trying to call WorksheetFunction.SumProduct within a loop.

What I am trying to achieve: Filtering for two variables succesively, and then creating ranges of the visible cells only to calculate a volume-weighted average using the sumproduct function.

Any help is much appreciated!

Please find my code below:

With wsS
     .AutoFilterMode = False
        With Range("B1:U" & lRow)
                For i = 0 To uBS - 1 'Step -1
                    '.AutoFilter Field:=10, Criteria1:=sArray(i) ', Operator:=xlFilterValues
                    .AutoFilter Field:=10, Criteria1:=result(i)
                        For j = 0 To uBD - 1
                            .AutoFilter Field:=8, Criteria1:=result2(j)
                            endrowTs = Worksheets(10).Cells(Rows.Count, 2).SpecialCells(xlCellTypeLastCell).End(xlUp).Row
                                If Not endrowTs < 2 Then
                                    With Worksheets(10).Range("M2:M" & endrowTs)
                                    Set r1 = .SpecialCells(xlCellTypeVisible)
                                    End With
                                    With Worksheets(10).Range("M2:M" & endrowTs)
                                    Set r2 = .SpecialCells(xlCellTypeVisible)
                                    End With
                                    VolG = WorksheetFunction.SumProduct(r1, r2)
                                Else
                                End If
                            
                        Next j
                Next i
        End With
    .AutoFilterMode = False
End With

Already tried a lot but nothing helped so far, e.g.

VolG = Application.WorksheetFunction.SumProduct(.Range("L2:L" & lRow).SpecialCells(xlCellTypeVisible), .Range("M2:M" & lRow).SpecialCells(xlCellTypeVisible))

Answer 1

On the sheet below you can see illustration how we can use SUMPRODUCT function on a filtered range. You can use either formulas or VBA code. Possible versions of formulas:

=SUMPRODUCT($C$3:$C$9,$D$3:$D$9,SUBTOTAL(3,INDIRECT("B"&ROW($B$3:$B$9))))
=SUMPRODUCT($C$3:$C$9,$D$3:$D$9,SUBTOTAL(3,OFFSET($B$3,ROW($B$3:$B$9)-ROW($B$3),0)))

3 versions of VBA code (all of them work, however I didn't compare their speed for bigger ranges).

Sub SumProductWithFilter1()
       Dim Subtot As Double, V As Variant, r As Long
       With WorksheetFunction
              V = Range("B3:B9").Value
              For r = 1 To UBound(V)
                      V(r, 1) = .Subtotal(3, Range("B3:B9")(r))
              Next r
              Subtot = .SumProduct(Range("C3:C9").Value, _
                       Range("D3:D9").Value, V)
       End With
       If IsNumeric(Subtot) Then MsgBox Subtot
End Sub

Sub SumProductWithFilter2()
       Dim Subtot As Double, V As Variant, c As Long
        V = Range("B3:B9").Value
              For c = 1 To UBound(V)
                      V(c, 1) = 1 + CLng(Range("B3:B9")(c).Rows.Hidden)
              Next c
       With WorksheetFunction
              Subtot = .SumProduct(Range("C3:C9").Value, _
                       Range("D3:D9").Value, V)
       End With
       If IsNumeric(Subtot) Then MsgBox Subtot
End Sub

Sub SumProductWithFilter3()
       Dim Subtot
       Subtot = ActiveSheet.Evaluate("SUMPRODUCT(C3:C9,D3:D9,SUBTOTAL(3,OFFSET(B3,ROW(B3:B9)-ROW(B3),0)))")
       If IsNumeric(Subtot) Then MsgBox Subtot
End Sub