Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wtih sumproduct 2 help
hello, i though my problem was solved by it is not
i am not able to make my function to work, it is govong me #value! for a result here is my sub ------------------------------------------------------- Sub test() Dim numberOfViolations As Variant 'if i will declare this as long getting type mismatch For i = 7 To 500 If Sheets("Summary").Cells(i, "A").Value = "" Then Exit Sub End If numberOfViolations = [SUMproduct((Detail!$B$7:$B$500=$A$"&i&")*(Detail!$ D$7:$D$500="Offender Missed Call (STaR)"))] Sheets("Summary").Cells(i, "I").Value = numberOfViolations Next i End Sub ---------------------------------------------- when compiling it is not giving me any errors, its just simply doesnt work gives me #value! as i said before. PS! what i am trying to do is to count how many entries in detail meet that criteria from formula, first critera will be alway different and it will be taken from A7, A8, A9, thats why i dont have a specific name there and i used $A$"&i&" as one guy suggested from my previous post, please really need help, thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wtih sumproduct 2 help
Back to my original post,
Well, there was a typo in that a paren was left off the end (to close the evaluate function). However, after I fixed that it worked fine. Sub abc() For i = 7 To 10 cnt = Evaluate("SUMPRODUCT((Detail!$D$7:$D$500" & _ "=""Offender Missed Call(STaR)"")*(Detail!$B$7:$B$500=Summary!$A$" & _ i & "))") Worksheets("Summary").Cells(i, "I").Value = cnt Next i End Sub Tested successfully. -- Regards, Tom Ogilvy wrote in message ups.com... hello, i though my problem was solved by it is not i am not able to make my function to work, it is govong me #value! for a result here is my sub ------------------------------------------------------- Sub test() Dim numberOfViolations As Variant 'if i will declare this as long getting type mismatch For i = 7 To 500 If Sheets("Summary").Cells(i, "A").Value = "" Then Exit Sub End If numberOfViolations = [SUMproduct((Detail!$B$7:$B$500=$A$"&i&")*(Detail!$ D$7:$D$500="Offender Missed Call (STaR)"))] Sheets("Summary").Cells(i, "I").Value = numberOfViolations Next i End Sub ---------------------------------------------- when compiling it is not giving me any errors, its just simply doesnt work gives me #value! as i said before. PS! what i am trying to do is to count how many entries in detail meet that criteria from formula, first critera will be alway different and it will be taken from A7, A8, A9, thats why i dont have a specific name there and i used $A$"&i&" as one guy suggested from my previous post, please really need help, thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro with SUMPRODUCT help | Excel Programming | |||
How to use SUMPRODUCT in macro? | Excel Programming | |||
Help wtih date and time in the same cell | Excel Worksheet Functions | |||
Help needed wtih Vlookup | Excel Programming | |||
How do I use Excel9.olb with computers wtih Excel 97? | Excel Programming |