DogLover;564101 Wrote:
Just wanting to count multiple criteria and plan to expand this function
once
I know it is error free. I think I have some format off or something,
please
help I get the message "type mismatch".

mquestion1range has numerical data which should sum if the
mTimeCriteria is
found to match.

Dim mTimeCriteria, mPositionCriteria As String
Dim mQuestion1Range, mTimeRange, mPositionRange As Range
Dim mFormula As String
Dim mCount As Long

mTimeCriteria = "First day of employment (Time 1)"
mPositionCriteria = "Registered Nurse"
Set mPositionRange = Worksheets("Data").Range("DataPosition")
Set mTimeRange = Worksheets("Data").Range("DataTime")
Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")J

MsgBox Evaluate("=SUMPRODUCT( --(mTimeRange= " & mTimeCriteria &
")*(mQuestion1Range) )")

Not looked too closely at this but what sticks out a mile is the Dim
statements. You may want to specify the data type of each variable
separately, otherwise those not explicitly typed will be variants:
Dim mTimeCriteria, mPositionCriteria As String
Dim mQuestion1Range, mTimeRange, mPositionRange As Range
becomes:
Dim mTimeCriteria As String, mPositionCriteria As
String
Dim mQuestion1Range As Range, mTimeRange As Range, mPositionRange As
Range
Still guessing.. I would have thought that perhaps you're looking for
the likes of this:
MsgBox Evaluate("=SUMPRODUCT(--(" & mTimeRange.Address
& " =""" & mTimeCriteria & """)*" & mQuestion1Range.Address & "
)")
instead of:
MsgBox Evaluate("=SUMPRODUCT( --(mTimeRange= " &
mTimeCriteria & ")*(mQuestion1Range) )")
Have you tried using SumProduct via:
Application.Worksheetfunction.Sumproduct
You supply ranges, strings etc. but you don't have to worry about
multiple quotation marks.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=155665

Microsoft Office Help