Probably NG wrap-around. Try
j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
CLng(nStuff) & "),--(IsNumber(Find(""cxl"",H1:H3000))))")
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Michael A" wrote in message
...
Hi Bob,
I am getting a "Type Mismatch" error on that line. I'm not sure what even
causes that... Is there anything else I need to do with this line? Thanks!
Michael A" wrote:
Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.
I'll try this. Thanks for your help.
- Mike
"Bob Phillips" wrote:
That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and
Application.FIND.
Bt even then, SP won't resolve using that technique. I always use
Evaluate
in VBA
j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Michael A" wrote in message
...
Hi Bob,
Thank you for the response. Do you think that I could do this with
VB
script? the value "nstuff" is from a userinput box asking for the
date.
j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))
It dosn't seem to know what "FIND" is. Any suggestions?
"Bob Phillips" wrote:
=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I
use a
wild
card. I need to count how many entries in column H: have "CXL"
in the
text.
here is what I have
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)
if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?
Thanks!