mixing of vba and sumproduct
 

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("OrderMonthNam e") =
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

mixing of vba and sumproduct
 

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)] )

mixing of vba and sumproduct
 

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.... =(


"abcd" wrote:


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)] )

mixing of vba and sumproduct
 

J,

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


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

HTH,
Bernie
MS Excel MVP


"Gixxer_J_97" wrote in message
...
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("OrderMonthNam e") =
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

mixing of vba and sumproduct
 

You rock!

thanks!

J

"Bernie Deitrick" wrote:

J,

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


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

HTH,
Bernie
MS Excel MVP


"Gixxer_J_97" wrote in message
...
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("OrderMonthNam e") =
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

mixing of vba and sumproduct
 

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...