Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)] ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)] ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mixing zip with zip+4 and then sorting | Excel Discussion (Misc queries) | |||
Mixing text and alpha in sumproduct arrays | Excel Worksheet Functions | |||
mixing up a list | Excel Discussion (Misc queries) | |||
Mixing up the arguments | Excel Worksheet Functions | |||
Newbie at mixing ADO and Excel | Excel Programming |