Excel VBA Sumproduct
Gimp,
You need to double up internal double quote marks:
cntc = Evaluate("=SUMPRODUCT((C3:C35<"""")/COUNTIF(C3:C35,C3:C35&""""))")
MsgBox cntc
HTH,
Bernie
MS Excel MVP
"Gimp" wrote in message
oups.com...
I've been working through what I think should be rather easy, but i'm
here asking ...
I have a very simple excel sheet that will have 5 to 50 rows or so
filled in, with 15 columns. What I want to do is count the unique
values in column C with vba code. Right now I'm using the following
formula: =sumproduct((C3:C35<"")/COUNTIF(C3:C35,C3:C35&""))") which
works well.
I'm trying to accomplish the same result with VBA. I've tried:
cntc = ("=sumproduct(countif(c3:c35,c3:c35))") which returns a count
of 47, but the count should be 29.
I've tried:cntc = Evaluate("=SUMPRODUCT((C3:C35<"")/
COUNTIF(C3:C35,C3:C35&""))"), which returns 0.
I have
DIm cntc as Range
Set cntc = Range("c65536").End(xlUp).Offset(2, 0)
then one of my two attempts above...
I feel like I'm very close, but what am I missing?
|