AH, THANK YOU!

The only thing I changed was just leaving ClientNum as is instead of using
Format, since all values of ClientNum are greater than 1000 and, as such,
would not have any leading 0's. So, really, all that was necessary was to
add ".Address" to each of my ranges. Just to further my education, why is
that necessary here (i.e., with SUMPRODUCT) and not in other places where I
refer to those ranges?

Many, many thanks.

"Joe User" wrote:

"Luke" wrote:
Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that you
tried. I offered several to try.


1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I
mentioned before.


The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to the
entire range, to be sure that all of the range is the expected type, be it
numeric (1) or text (2).


2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that you
get a 4-character string. Note that the string "0012" is not equal to the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")


All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


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

"Luke" wrote in message
...
Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them in
the
right spot I'll sure appreciate it.

"Joe User" wrote:

"Luke" wrote:
I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is that
truly your intent? Is that the type of values in the cells represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" &
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


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

"Luke" wrote in message
...
I've looked through the various posts concerning this, but still can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

.


.