Sumproduct in VBA Type Mismatch problem

[DogLover]

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

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Barb Reinhardt]

You may need to change

Dim mQuestion1Range, mTimeRange, mPositionRange As Range

to

Dim mQuestion1Range as Range
Dim mTimeRange as Range
Dim mPositionRange As Range

 

[Joe User]

I had no problem when I copy-and-pasted the fragment of your macro, with the
following corrections.

Dim mQuestion1Range, mTimeRange, mPositionRange As Range

That works as-is. But I believe the following is better style, and there
will be situations where it can make a difference.

Dim mQuestion1Range as Range, mTimeRange as Range, mPositionRange As Range

Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")J

Remove the "J", an obvious typo.

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

Replace with:

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

The primary problems were the placements of quotes, the failure to use
..Address where appropriate, and the lack of quotes (in the Evaluate string)
around mTimeCriteria.

I replaced effectively "*(mQuestion1Range.Address)" with
",mQuestion1Range.Address". That is optional an style issue, a personal
choice if the mQuestion1Range.Address range has only numbers and truly
empty cells (i.e. no formula and no constant).


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

 

[Joe User]

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

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/showthread.php?t=155665

Microsoft Office Help

 

[DogLover]

I think I have narrowed the problem down a little. I get a data type
mismatch message when I substitute the mQuestion1Range name in for the
actually range=Data!M3:M250. I commented out the first and it runs, then
when I use the range name there, I get the message. Is there something wrong
with my Dim or Set range statements for using a SUMPRODUCT.




Private Sub VBATEST_Click()

Dim mTimeCriteria, 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")


mTimeCriteria = """" & mTimeCriteria & """"
' Var = Application.Evaluate("SUMPRODUCT((Data!G3:A250 =" & mTimeCriteria &
")*(Data!M3:M250))")

Var = Application.Evaluate("SUMPRODUCT((Data!G3:A250 =" & mTimeCriteria &
")*(mQuestion1Range))")
MsgBox (Var)

End Sub

 

[Dave Peterson]

Option Explicit
Sub testme02()
Dim mTimeCriteria As String '????
Dim mPositionCriteria As String
Dim mQuestion1Range As Range
Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mFormula As String
Dim mCount As Long
Dim Res As Variant 'could be an error

mTimeCriteria = "First day of employment (Time 1)"
mPositionCriteria = "Registered Nurse"

With Worksheets("data")
Set mPositionRange = .Range("DataPosition")
Set mTimeRange = .Range("DataTime")
Set mQuestion1Range = .Range("DataQuestion1")

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address _
& "=" & Chr(34) & mTimeCriteria & Chr(34) _
& ")," & mQuestion1Range.Address & ")"

Res = .Evaluate(mFormula)
End With

If IsError(Res) Then
MsgBox "Error in evaluating"
Else
MsgBox Res
End If

End Sub

Notice that I used the with/end with structure -- even with the .evaluate
method. That means that the unqualified addresses (no workbook/worksheet names
included) will refer to that worksheet in the With statement.

If I had used Application.evaluate() or just Evaluate(), then the addresses
would have referred to the activesheet (if the code was in a General module).

I could have used:

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address(external:=true) _
& "=" & Chr(34) & mTimeCriteria & Chr(34) _
& ")," & mQuestion1Range.Address(external:=true & ")"

res = application.evaluate(mFormula)

ps. Notice that those strings have to be surrounded by double quotes--just like
in a formula in a cell:

=SUMPRODUCT(--(a1:a10="First day of employment (Time 1)"),b1:b10)

 

[DogLover]

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

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/showthread.php?t=155665

Microsoft Office Help

.

 

[DogLover]

This works! You are a lifesaver. It looks like the key was to add this
With Worksheets ("data") so it would recognize the actual data range.

 

[DogLover]

This is so close to what I need, but I need to have multiple criteria and
cannot seem to find the right syntax. I feel fairly certain once I can
expand for 1 more, I can do the others.

Here is what I'm trying to do, but it suggest I am missing a parenthesis,
and just don't see it. What would the formula be to add an additional
criteria like this?

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address _
& "=" & Chr(34) & mTimeCriteria & Chr(34) & ")," _
--(" & mPositionRange.Address _
& "=" & Chr(34) & mPositionCriteria & Chr(34) &
")," _
& mQuestion1Range.Address & ")"

 

[Dave Peterson]

It's not really the =sumproduct() formula that's wrong--it's your VBA syntax:

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address _
& "=" & Chr(34) & mTimeCriteria & Chr(34) & ")," _
& "--(" & mPositionRange.Address _
& "=" & Chr(34) & mPositionCriteria & Chr(34) & ")," _
& mQuestion1Range.Address & ")"