Sumproject Run-time Error 13

D

DogLover

I have this code and am having trouble with the Sumproduct working. I get a
Run-Time Error 13 and Type Mismatch message? Can anyone help.

Dim mTimeCriteria As String
Dim mQuestion1Range As Range, mTimeRange As Range

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

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

Per Jessen

Hi

I tried to split up your code a bit, and found that the line commented
out below is not needed:

Dim mTimeCriteria As String
Dim mQuestion1Range As Range, mTimeRange As Range
Dim MyFormula As String

mTimeCriteria = "First day of employment (Time 1)"
Set mTimeRange = Worksheets("Data").Range("DataTime")
Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")
'mTimeCriteria = """" & mTimeCriteria & """"
MyFormula = "=SUMPRODUCT( --(" & mTimeRange.Address & "= """ &
mTimeCriteria & """), " & mQuestion1Range.Address & ")"
Result = Evaluate(MyFormula)

MsgBox Result

Hopes this helps.
....
Per
 
J

Jacob Skaria

Try the below...You already have added double quotes ....mTimeCriteria = """"
& mTimeCriteria & """". So you can remove the double double quotes mentioned
within the evaluate()


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

If this post helps click Yes
 
D

Dave Peterson

Check one of your other messages.
I have this code and am having trouble with the Sumproduct working. I get a
Run-Time Error 13 and Type Mismatch message? Can anyone help.

Dim mTimeCriteria As String
Dim mQuestion1Range As Range, mTimeRange As Range

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

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

DogLover

This is getting closer. Both your suggestions eliminated the Error message.
The formula is evaluating to 0. It should be 915 if I have everything
correct.

The formula in my spreadsheet which evaluated to 915 is:=SUMPRODUCT(
--(DataTime="First day of employment (Time 1)"),( DataQuestion1) )
Do you see something off in the way it is in the VBA format that would make
it correct?
 
J

Jacob Skaria

You need to specify that the address inclusive of sheet name.... as below

Dim mTimeCriteria As String
Dim mQuestion1Range As Range, 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(External:=True) & _
"= """ & mTimeCriteria & """), " & _
mQuestion1Range.Address(External:=True) & ")")

'OR' since you have only one condition you can use sumif() as below

MsgBox WorksheetFunction.SumIf(mTimeRange, mTimeCriteria, mQuestion1Range)


If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top