Formula to count distinct values not working in VBA
 

It need to count unique TEXT values in a column. Sorry for the typo. :-)

"ALATL" wrote:

I have this formula that works perfectly in Excel. It counts the unique
values in a column. But it does not work in the VBA editor. Does anyone have
any ideas?

Count = SUMPRODUCT((C6:C39<"")/COUNTIF(C6:C39,C6:C39&""))

Formula to count distinct values not working in VBA
 

You can something like:

dim myCount as long

myCount = _
ActiveSheet.Evaluate("SUMPRODUCT((C6:C39<"""")/COUNTIF(C6:C39,C6:C39&""""))")

Subject to the same rules as using =sumproduct() in a worksheet cell.

ALATL wrote:

It need to count unique TEXT values in a column. Sorry for the typo. :-)

"ALATL" wrote:

I have this formula that works perfectly in Excel. It counts the unique
values in a column. But it does not work in the VBA editor. Does anyone have
any ideas?

Count = SUMPRODUCT((C6:C39<"")/COUNTIF(C6:C39,C6:C39&""))

--

Dave Peterson

Formula to count distinct values not working in VBA
 

Will not work if spaces or range extends beyond data

mycount = Evaluate("SUMPRODUCT(1/COUNTIF(G1:G8,G1:G8))")

--
Don Guillett
SalesAid Software

"ALATL" wrote in message
...
It need to count unique TEXT values in a column. Sorry for the typo. :-)

"ALATL" wrote:

I have this formula that works perfectly in Excel. It counts the unique
values in a column. But it does not work in the VBA editor. Does anyone
have
any ideas?

Count = SUMPRODUCT((C6:C39<"")/COUNTIF(C6:C39,C6:C39&""))