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

"p45cal" wrote:
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.

I wanted to make the same suggestion. But I could not make the syntax work
for the exact logic that DogLover wanted to evaluate.

Can you provide a working example using WorksheetFunction.SumProduct?

The working example must evaluate the following Excel equivalent, using the
variables in DogLover's posting:

=SUMPRODUCT(--(DataTime="First day..."),DataQuestion1)

where DataTime and DataQuestion1 are named ranges.


----- original message -----

"p45cal" wrote in message
...

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

I have totally simplified to see where the problem might be. Here is the
simplified code. I still get a data type mismatch. I think it is something
to do with the mQuestion1Range and mTimeRange. How would you rewrite this to
use Application.Worksheetfunction.Sumproduct??

Dim mTimeCriteria
Dim Var As Variant
Dim mQuestion1Range As Range
Dim mTimeRange As Range

mTimeCriteria = "First day of employment (Time 1)"
Set mTimeRange = Worksheets("Data").Range("DataTime")
Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")

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


End Sub

"p45cal" wrote:


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

.

DogLover;564261 Wrote:
How would you rewrite this to use
Application.Worksheetfunction.Sumproduct??
With difficulty. I'll withdraw the suggestion and I'm with Joe User on
this one.

Sticking with Evaluate you could simplify the code by not having to set
variables to ranges by using the existing names directly in the formula,
as you would on the sheet:
MsgBox Evaluate("=SUMPRODUCT(--(DataTime=""" &
mTimeCriteria & """)*DataQuestion1)")
So after removing everything extraneous to the evaluate line all you
need is:
Dim mTimeCriteria As String
mTimeCriteria = "First day of employment (Time 1)"
MsgBox Evaluate("=SUMPRODUCT(--(DataTime=""" & mTimeCriteria &
""")*DataQuestion1)")


--
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