Type mismatch error using Sumproduct

[Luke]

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.

 

[Dave Peterson]

If you were writing the formula in a cell, it would look something like:
=SUMPRODUCT((HoursCodes="A")*(HourStat="x")*PeriodHours)

Since both the classcode and clistat values are strings, they need to be
surrounded by double quotes.

=chr(34) is a double quote in VBA land.



Option Explicit
Sub testme()

Dim TotalHrs As Double 'don't use Single
Dim ClassCode As String
Dim CLIStat As String
Dim myFormula As String

ClassCode = "A"
CLIStat = "x"

'=SUMPRODUCT((HourCodes="a")*(HourStat="x")*PeriodHours)
With Worksheets("Hours")

myFormula = "SUMPRODUCT(" _
& "(HoursCodes=" & Chr(34) & ClassCode & Chr(34) & ")" _
& "*(HourStat=" & Chr(34) & CLIStat & Chr(34) & ")" _
& "*PeriodHours)"

TotalHrs = .Evaluate(myFormula)

End With

MsgBox TotalHrs

End Sub

I find this more difficult to read (most the time!), but you could use it if you
like it better:

myFormula = "SUMPRODUCT(" _
& "(HoursCodes=""" & ClassCode & """)" _
& "*(HourStat=""" & CLIStat & """)" _
& "*PeriodHours)"

======
ps. The reason I used a separate string variable for the formula is so that I
could step through the code, debug.print the formula to the immediate window and
copy|Paste into a cell in excel proper.

Lots of times, I like to get excel's help with my formula typing errors.

pps. There's no reason ever to use Single or Integer. Always use Double and
Long. Modern computers will have less work to do and you don't have to worry
about overflow errors.

 

[Luke]

I'm still getting the type-mismatch error, although now it is at the
"TotalHrs = .Evaluate(myFormula)" line. CLIStat is actually an Integer (0,
1, or 99), or Long if you prefer. Will that make a difference in the formula
you gave? I did change TotalHrs to Double and CLIStat to Long, however.

 

[Dave Peterson]

If CLIStat is a number, then remove the double quotes from that portion of the
formula.

But that won't make the formula cause an error like this. It just won't find a
match, so those rows will be ignored/treated as 0.

I'm guessing that at least one of those ranges contains errors.

Can you get the equivalent formula to work in a worksheet cell (just do it
manually)? If you do, then share that formula in the follow-up.

 

[Joe User]

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

 

[Joe User]

Errata....

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

First, I neglected to notice that ClassCode is a String variable. So its
value needs to be quoted in the Evaluate string, just as it would be quoted
in the SUMPRODUCT expression in the worksheet. To wit:

sEval = "SUMPRODUCT((" & HoursCodes.Address & "=""" & _
ClassCode & """)*(" & HourStat.Address & "=" & CliStat & ")," _
& PeriodHours.Address & ")"

Second, I did not realize that Dave had posted nearly the same idea. The
key difference is the need for .Address when referencing the Range variables.
Also note that CliStat is not quoted. I assume that the values in the
HourStat range are numeric, not text.


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

 

[Luke]

The sweet smell of success! Thank you so very much. It was the ".Address"
part that did it. I also used Dave's suggestion for the Chr(34) instead of
the double quotes as my eyes start having troubles when all those quotes
marks get together.

Whew. No more For-Next loops evaluating each of my 1265 rows of data.

Thanks all.

 

[Dave Peterson]

I thought that HoursCode and HourStat were both range names in the worksheet.

Sorry.