"Luke" wrote:
TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
(HourStat=CliStat)*PeriodHours)")
End With

TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.

You cannot reference VBA variables directly in the quoted string passed to
Evaluate. This is error-prone and difficult to read. So it is prudent to
build the Evaluate argument in a string variable that you can display in
debug mode or print to the Immediate window. For example:

Dim sEval as String
sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
& PeriodHours.Address & ")"
TotalHrs = .Evaluate(sEval)

Aside.... TotalHrs should probably be type Double, not Single. Generally,
all floating point variables should be type Double, especially if some of
them will be stored into or compared with worksheet values. The reason is
complicated to explain fully. In a nutshell, the conversion from Single to
Double is imperfect.


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

"Luke" wrote:
I've searched through similar topics and tried several variations of code,
but I keep getting a type-mismatch error when trying to use Sumproduct. I am
wanting to use this in several parts of my code. The first instance (which
is also where the error keeps occurring) is below.

With Worksheets("Hours")
TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
(HourStat=CliStat)*PeriodHours)")
End With

TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
long), CliStat as Integer (0, 1, or 99), and the rest as Ranges. I've tried
adjusting the syntax in every way I can think of and based on other examples
I could find, but each time I get the type-mismatch error. Do I just have
something dim'd incorrectly?

Thanks for time and help.