Sumproduct in VBA

[habman]

I have the following SUMPRODUCT formula that I would like to convert to VBA using the this command Application.WorksheetFunction.SumProduct


Here is my working formula in Excel at Cell "E1"

=SUMPRODUCT(--($B$1:$B$1000=$A1),$C$1:$C$1000,$D$1:$D$1000)


I have the formula partially coded in VBA but I am having problems with coding the first argument

Here is my VBA

Range("E1").Formula = Application.WorksheetFunction.SumProduct( ?????? , Range("C1:C1000"), Range("D1:D1000"))


Obviously I don't know how to code the ????? in VBA

Thanks.

 

[VoG]

Try

Range("E1").formula="=SUMPRODUCT(--($B$1:$B$1000=$A1),$C$1:$C$1000,$D$1:$D$1000)"

 

[habman]

Try

Range("E1").formula="=SUMPRODUCT(--($B$1:$B$1000=$A1),$C$1:$C$1000,$D$1:$D$1000)"

Okay I messed up. I tried to simplify my example and in doing so I added Range("E1").formula to my VBA.


I actually want to assign my equation to a variable in my VBA. Something like this:

mySumProduct = Application.WorksheetFunction.SumProduct( ?????? , Range("C1:C1000"), Range("D1:D1000"))

 

[VoG]

Try

mylookup = Evaluate("SUMPRODUCT(--($B$1:$B$1000=$A1),$C$1:$C$1000,$D$1:$D$1000)")

 

[habman]

Try

mylookup = Evaluate("SUMPRODUCT(--($B$1:$B$1000=$A1),$C$1:$C$1000,$D$1:$D$1000)")

PERFECT!!

Thank you so much.