excel vba - How to manage sumproduct with VBA? -

excel vba - How to manage sumproduct with VBA? -

i wrote next code: i'm trying utilize sumproduct function vba doesn't work. i've tried evaluate function got same bad result.

dim row long dim rows range dim integer dim rng range dim rngl range dim rngo range dim rngp range sheets("report") lastrow = .range("c" & .rows.count).end(xlup).row end set rng = thisworkbook.sheets("report").range("g3:g" & lastrow) set rngl = thisworkbook.sheets("report").range("f3:f" & lastrow) set rngo = thisworkbook.sheets("report").range("c3:c" & lastrow) = 3 33 range("e" & us).value = application.evaluate("=sumproduct((sheets(luglio).range(c & us))<rng)*(sheets(luglio).range(c & us)=rngl)") next

as rory mentions in comments, isn't working because vba passing entire string within quotations marks excel. need concatenate strings desired output see in excel formula.

try replacing line:

range("e" & us).value = application.evaluate("=sumproduct((sheets(luglio).range(c & us))<rng)*(sheets(luglio).range(c & us)=rngl)")

with:

range("e" & us).value = "=sumproduct((luglio!c" & & "<report!g3:g" & lastrow & ")*(luglio!c" & & "=report!f3:f" & lastrow & "))"

excel-vba