Sumproject Run-time Error 13

  • Thread starter Thread starter DogLover
  • Start date Start date
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 & ")")
 
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
 
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
 
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 & ")")
Click to expand...
 
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?
 
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
 
Back
Top