Hi ,

I am trying to evaluate a sumproduct in VBA and it is acting very oddly.

The code i am trying to evaluate is:

MsgBox Evaluate("=SUMPRODUCT((dType=""" & pType & """)*(dStage=" & pStage & ")*(dRoleType=""" & pRole & """)*(dActivity=""" & pActivity & """)*(dCategory=""" & cCat & """)*(dBand=1)*dResource)")
either of the following two formulas works:

                    MsgBox Evaluate("=SUMPRODUCT((dType=""" & pType & """)*(dStage=" & pStage & ")*(dRoleType=""" & pRole & """)*(dActivity=""" & pActivity & """)*(dBand=1)*dResource)")
                    MsgBox Evaluate("=SUMPRODUCT((dType=""" & pType & """)*(dStage=" & pStage & ")*(dRoleType=""" & pRole & """)*(dCategory=""" & cCat & """)*(dBand=1)*dResource)")
However the first one give me a type-mismatch error.

Does anyone know why please?

p.s. dType, dStage, dCategory, are all named ranges in the workbook (all same size).
pRole, pStage, etc are all variables which are defined earlier