Difficult question: SUMPRODUCT function in VBA code

[pmdown]

Hallo.

In worksheet I use this SUMPRODUCT function:

=SUMPRODUCT((List1l!$D$2:$D$1000=$A6)*(List1!$A$2:$A$1000<=$A$2)*(List1!$G$2:$G$1000)))

Now I need use this same formula, but in VBA code... I have try many ways to do this, but not one works...

Can You help me ? Thank's.

 

[Albert 1]

With the result in H1:

Sub test1()
Range("H1").Formula = " = SUMPRODUCT((List1!$D$2:$D$1000=$A6)*(List1!$A$2:$A$1000<=$A$2)*(List1!$G$2:$G$1000)))"
End Sub

Albert 1

 

[Andrew Poulsom]

Unfortunately in VBA the True/False results are not coerced into values like they are on a worksheet, and a type mismatch occurs.

Have a look here:

http://216.92.17.166/board2/viewtopic.php?t=49311

 

[pmdown]

Thanks Albert 1, but i don't want formulas in worksheet. I need numeric values only... that´s just I remake this into VBA code...


I Thanks Andrew Poulsom too. I already saw this older post, but there is no answer for my problems, because I use sign * in my formula...


Have anybody better Idea ? Thank's. Peter.[/b]

 

[Andrew Poulsom]

I think that post gives you what you want. Basically you create arrays of 1's and 0's and use those:

Sub Test() 
    Dim WF As WorksheetFunction 
    Dim a As Range, b As Range, c As Range 
    Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant 
    Dim i As Integer 
    Set WF = Application.WorksheetFunction 
    Set a = Worksheets("List1").Range("$D$2:$D$1000") 
    Set b = Worksheets("List1").Range("$A$2:$A$1000") 
    Set c = Worksheets("List1").Range("$G$2:$G$1000") 
    Arr1 = WF.Transpose(a) 
    For i = 1 To UBound(Arr1) 
        If Arr1(i) = Range("A6").Value Then 
            Arr1(i) = 1 
        Else 
            Arr1(i) = 0 
        End If 
    Next i 
    Arr2 = WF.Transpose(b) 
    For i = 1 To UBound(Arr2) 
        If Arr2(i) = Range("A2").Value Then 
            Arr2(i) = 1 
        Else 
            Arr2(i) = 0 
        End If 
    Next i 
    Arr3 = WF.Transpose(c) 
    Range("H1").Value = WF.SumProduct(Arr1, Arr2, Arr3) 
End Sub

 

[pmdown]

Thank You Andrew Poulsom. I have examine Yuor code, and I have realised. Yes, Your code appears functional...


...but I have in my sheet hundrets of varied SUMPRODUCT formulas. I cannot imagine, each one FORMULA replace with this 30row code. Ufff !!! This could be also thousands rows of code.... Nooo.


SUMPRODUCT formula I use to get SUM for multiple filter criteria. Is there a elegant way to get SUM in column for multiple filter criteria in VBA code?


Thank You. Peter.

 

[Andrew Poulsom]

If your purpose is to save memory by converting the SUMPRODUCT formulas to values, you can use this approach:

Suppose the formula in H1 would be:

=SUMPRODUCT((List1!$D$2:$D$1000=$A6)*(List1!$A$2:$A$1000<=$A$2)*(List1!$G$2:$G$1000))

and you want to fill the range H1:H50.

Sub Test()
    With Range("H1:H50")
        .Formula = "=SUMPRODUCT((List1!$D$2:$D$1000=$A6)*(List1!$A$2:$A$1000<=$A$2)*(List1!$G$2:$G$1000))"
        .Value = .Value
    End With
End Sub

That will post the formulas, calculate the results and convert to values.

 

[PaLPiTaTioN]

I want to use the SUMPRODUCT-function in VBA as a worksheetfunction:

Variable = Application.WorksheetFunction.SumProduct(...)

How should I reference to the ranges in the workbook? I tried to use the sheet names, but to no avail.