Sumproject Run-time Error 13

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

 

[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

 

[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

 

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

 

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

 

[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