function "sumproduct" in VBA environment
 

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("y ard") = "a"),
(Range("ac_name") = "b"), Range("amt"))
End Sub

!!!Thank you so much!!!

Best rgds // aw

--
aw

function "sumproduct" in VBA environment
 

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

HTH
Cordially
Pascal

"aw" a écrit dans le message de news:
...
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("y ard") = "a"),
(Range("ac_name") = "b"), Range("amt"))
End Sub

!!!Thank you so much!!!

Best rgds // aw

--
aw

function "sumproduct" in VBA environment
 

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

HTH
Cordially
Pascal

"papou" a écrit dans le message
de news: ...
Hello
If your sumproduct formula works, use Evaluate:
MyAns = Evaluate("=SUMPRODUCT(--(yard="BSHKY"),--(ac_name="Yard
Trucks"),amt)")

HTH
Cordially
Pascal

"aw" a écrit dans le message de news:
...
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("y ard") = "a"),
(Range("ac_name") = "b"), Range("amt"))
End Sub

!!!Thank you so much!!!

Best rgds // aw

--
aw