..find doesn't work when called from a worksheet function in some versions of
excel.

It does work in xl2002, though.



DOTJake wrote:

I am still trying to get a count of unique values in a filtered column.

The formula which someone posted does not work. (I get a #NAME error) from the following formula:

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10))-(COUNTA(A6:A10)<SUBTOTAL(3,A6:A10))

I tried writing a function in VB as follows using the 'find' method:

Function CtUnqFiltered(rng As Range) As Long
Dim fc As Excel.Range
Dim ct As Long
ct = 0
For Each c In rng.Cells
If (Not rng.Rows(c.Row - rng.Rows.Count).Hidden) And (Not IsEmpty(c)) Then
Set fc = rng.Find(what:=c.Value, after:=c)
If (fc Is Nothing) Then ct = ct + 1
End If
Next
CtUnqFiltered = ct
End Function

This function should work, only counting the last of a unique value in the list, using the 'Find' The problem is that the 'Find' always returns nothing(never finds the value), even if there is another of the same value later in the range. Am i doing something wrong with the 'find'? The doc says it should return a the cell where it finds the value.

TIA,
DOTJake

--

Dave Peterson