hello
i am working on my project, that will count amount of entries from one
sheet by two criterias, and will insert that amount into another sheet,
and i have no idea what is wrong with this funtion that i wrote, it is
giving me an error everytime ("type mismatch")
here is my funtion

Sub countViolations()

Sheets("Summary").Select
Range("A7").Select

If ActiveCell = "" Then
Exit Sub
Else
Dim nameOfOffender As String
Dim VCount As Long
Dim nameOfViolation As String

nameOfViolation = "Offender Missed Call (STaR)"

nameOfOffender = Range("A7")
Sheets("Details").Activate
VCount = Application.WorksheetFunction.Sum((nameOfOffender) *
(nameOfViolation))
Sheets("Summary").Range("I7") = VCount
End If

End Sub

i am very new at this, it is my second day dealing with VBA, so if
something or everything is extremely wrong with this function please
dont laught :) thx

Write the formula that works when entered in the worksheet and perhaps we
can help.

You code as written looks like it is trying to multiply two sentences or a
sentence and a word together.

In any event, your subject says sumproduct, but you are using sum. I
suspect you want to use either as an array formula - which isn't supported
by using WorksheetFunction. If you post the formula that works when entered
in the spreadsheet, someone can show you how to code it. Include what sheet
each range is on.

Another option would be

Sheets("Summary").Range("I7").FormulaArray = "=formula that works"

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
hello
i am working on my project, that will count amount of entries from one
sheet by two criterias, and will insert that amount into another sheet,
and i have no idea what is wrong with this funtion that i wrote, it is
giving me an error everytime ("type mismatch")
here is my funtion

Sub countViolations()

Sheets("Summary").Select
Range("A7").Select

If ActiveCell = "" Then
Exit Sub
Else
Dim nameOfOffender As String
Dim VCount As Long
Dim nameOfViolation As String

nameOfViolation = "Offender Missed Call (STaR)"

nameOfOffender = Range("A7")
Sheets("Details").Activate
VCount = Application.WorksheetFunction.Sum((nameOfOffender) *
(nameOfViolation))
Sheets("Summary").Range("I7") = VCount
End If

End Sub

i am very new at this, it is my second day dealing with VBA, so if
something or everything is extremely wrong with this function please
dont laught :) thx

well, i dont think that i will need a formula, reason why is that i am
not callculating any data, i am trying to count how many rows will meet
my criteria, so i can create a summary sheet, and plug in amout of
violations accurent for this secific offender, so what i was doing is
trying to get a number of occurences with that code that i wrote :( i
guess it doesnt work that way than :) well ill keep trying, thank you
for a quck reply though

Heeey :)
ok i created a formula in my excel sheet that will do what i need, but
now i guess my question will be this how can i make this formula
through macro and i dont want just to add
a formula to my cell as i read somewhere it can be done like this

ActiveCell.FormulaR1C1Local = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10
="another criteria"))"

that wouldnt work for me, i would want this formula to be added through
macro

My formula:

=SUMPRODUCT((Detail!$D$7:$D$500="Offender Missed Call
(STaR)")*(Detail!$B$7:$B$500=Summary!$A$7))

because i would need to program cell A7 to add 1every time, so once it
will be in the loop it ill be than A8, a9, a10 and so on, i know how to
do that part :) but i have no idea on how to use sumproduct through
macro proper way, thanks

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

--
Regards,
Tom Ogilvy




wrote in message
ps.com...
Heeey :)
ok i created a formula in my excel sheet that will do what i need, but
now i guess my question will be this how can i make this formula
through macro and i dont want just to add
a formula to my cell as i read somewhere it can be done like this

ActiveCell.FormulaR1C1Local = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10
="another criteria"))"

that wouldnt work for me, i would want this formula to be added through
macro

My formula:

=SUMPRODUCT((Detail!$D$7:$D$500="Offender Missed Call
(STaR)")*(Detail!$B$7:$B$500=Summary!$A$7))

because i would need to program cell A7 to add 1every time, so once it
will be in the loop it ill be than A8, a9, a10 and so on, i know how to
do that part :) but i have no idea on how to use sumproduct through
macro proper way, thanks

YESSSSSSSSSSSSSSSSSSS! :) thank you, you are amazing, thank you thank
you thank you

:( it was too early to get excited, i have tryed to modify everything i
could, and it is still not workig the way it should, it was giving me
an error message at first at compilation, than i moved quotation marks,
and it was not giving me compilation error anymore how ever it is not
giving me a desired count result it is giving me #value!,

when you are using sumproduct through evaluate and you want to assign
address that was declared previosly you have to do this
evaluate("sumproduct("&range1.address&"=10)*("&ran ge2.address&"=5)")

so you have you have your declared range followed by address, which is
telling to excel that it is address, so isnt there supposed to be
something for a value as well, since i know range there is no reason to
declare a range, so i need to tell to macro that my criteria is a
criteria and not something else, hmmmmm i dont know but previos code
didnt work unfortinatly

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


--
Regards,
Tom Ogilvy


wrote in message
ups.com...
:( it was too early to get excited, i have tryed to modify everything i
could, and it is still not workig the way it should, it was giving me
an error message at first at compilation, than i moved quotation marks,
and it was not giving me compilation error anymore how ever it is not
giving me a desired count result it is giving me #value!,

when you are using sumproduct through evaluate and you want to assign
address that was declared previosly you have to do this
evaluate("sumproduct("&range1.address&"=10)*("&ran ge2.address&"=5)")

so you have you have your declared range followed by address, which is
telling to excel that it is address, so isnt there supposed to be
something for a value as well, since i know range there is no reason to
declare a range, so i need to tell to macro that my criteria is a
criteria and not something else, hmmmmm i dont know but previos code
didnt work unfortinatly

now it works, and my project is done, thanks to you Tom, thank you once
again!