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