Excel VBA Sumproduct

[Gimp]

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?

 

[Bernie Deitrick]

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