Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel VBA Sumproduct
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Excel : Nesting of functions such as sumproduct and sumif | Excel Worksheet Functions | |||
excel chart rendering problem when using apache java Poi package. | Excel Discussion (Misc queries) | |||
Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) |