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

Macro wtih sumproduct 2 help
 

Have you tried to debug your code by stepping through it? When you do
that you will see where the value is not getting assigned to the
variable that you expect it to be assigned to. Just position the cursor
over the variables as you go through each line of code. When you get
to the problem variable, you'll know what needs to be fixed.
I know you wanted someone to tell you what was wrong with your code but
I think that this can help as well.

Harold
wrote:
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

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