VBA sumproduct array

[ludo@excelforum]

hello,

I tried the following but it is not working ; I tried many syntaxes without any succes... can anyone helps me ? thx !!!

Sub sumprodludo()

Dim a() As Variant

Sheets("trades").Range("F7471:G7487").Select
a = Selection

ff = Application.WorksheetFunction.SumProduct(((application.Index(a, 0, 1)) > 100) * (Application.Index(a, 0, 2)))

End Sub

[:) Sixthsense :)]

Try like this...

Sub UsingSumproductInVBA()
Dim rMyRng As Range, rRng1 As Range, rRng2 As Range
Dim sFormula As String, vResult As Variant

Set rMyRng = Sheets("trades").Range("F7471:G7487")

With Application
    Set rRng1 = .Index(rMyRng, , 1)
    Set rRng2 = .Index(rMyRng, , 2)
End With

sFormula = "=SumProduct((" & rRng1.Address(, , , True) & "> 100) *" & rRng2.Address(, , , True) & ")"

vResult = Evaluate(sFormula)

MsgBox vResult

End Sub

[ludo@excelforum]

Hello,

thank sixthsense !!!
all works perfectly... it makes crazy long formulas but works... that's I hope I could use arrays and make logical filters on my array A...

another question : is it possible to make sub selections in a range or an array like other languages or like a sql/access request from excel ?
I mean is it possible to select only rows where cells(i,2)>0 and cells(i,3)="earth" (or A(i,2)>0 and Ai,3)="earth" with A an array) and to make calculation with the sub selected rows or the sumproduct is the only way ?

thx

[:) Sixthsense :)]

Some suggestions:-

• Using Looping system
• Using Countif in VBA
• Using Filter

Some more other methods may be available.....