function "sumproduct" in VBA environment

[aw]

Dear Sir / Madam,

Could anyone can help to translate the worksheet function – “sumproduct†(as
below) into VBA??


=SUMPRODUCT(--(yard="BSHKY"),--(ac_name="Yard Trucks"),amt)

Remark : “Yardâ€, “ac_name†& “amt†are pre-defined range.


My working as follow : (but run-time error ‘13’ type mismatch appear)

Sub sp()
a = InputBox("Yard")
b = InputBox("A/C name")
MyAns = Application.WorksheetFunction.SumProduct((Range("yard") = "a"),
(Range("ac_name") = "b"), Range("amt"))
End Sub

!!!Thank you so much!!!

Best rgds // aw

 

[papou]

Hello
If your sumproduct formula works, use Evaluate:
MyAns = Evaluate("=SUMPRODUCT(--(yard="BSHKY"),--(ac_name="Yard
Trucks"),amt)")

HTH
Cordially
Pascal

 

[papou]

Oops sorry, no equal sign needed:
MyAns = Evaluate("SUMPRODUCT(--(yard="BSHKY"),--(ac_name="Yard
Trucks"),amt)")

HTH
Cordially
Pascal