mixing of vba and sumproduct

[Guest]

hi all,

i would like to use vba and the sumproduct function

the excel formula version would be something like

=SumProduct((OrderMonthName=B1)*1)

i am trying
with sheets("Sheet1")
tot = tot + cdbl(Application.SumProduct((.Range("OrderMonthName") =
monthName(m))*1))
end with

but i am getting a type mismatch - i have a feeling i am using this
incorrectly.

any help?

tia!

J

 

[abcd]

1st , try:
Application.Worksheetfunctions.SumProduct

the:
try to remember under VBA, there is no matricial formula, so you can
not hope [ .Range("OrderMonthName") = monthName(m) ] to be a vector
(because there's no vector in VBA)

BUT you may try

tot = tot + cdbl( [SumProduct((OrderMonthName=B1)*1)] )

 

[Guest]

Hi - i did try that, and i'm getting a value of 2029 each time it loops -
which i'm pretty sure is the error code. so no luck yet.... =(

 

[Bernie Deitrick]

J,

For your example:
Application.Evaluate("SumProduct((OrderMonthName=B1)*1)")


For your code:
tot = tot + Application.Evaluate("SumProduct((OrderMonthName= """ _
& MonthName(m) & """)*1)")

HTH,
Bernie
MS Excel MVP

 

[Guest]

You rock!

thanks!

J

J,

For your example:
Application.Evaluate("SumProduct((OrderMonthName=B1)*1)")


For your code:
tot = tot + Application.Evaluate("SumProduct((OrderMonthName= """ _
& MonthName(m) & """)*1)")

HTH,
Bernie
MS Excel MVP

 

[abcd]

I'm surprise because i thought the evaluate function was (quite) the
same (in this case) than the [...] notation.

But, since it works now for you...