count unique values in a col

[miek]

I have the following code that looks at worksheet s1 and
counts the number of unique times it sees a passed varible,
and reports this value to worksheet s2
But it always returns with a zero. can someone help with the code?

Thxs
Dim Col_value as string

Col_value = "A"

Worksheets("S1").Activate
l_lastrow = Cells(Rows.Count, 1).End(xlUp).Row
l_ans = ActiveSheet.Evaluate("COUNTIF(" & Col_value & "1:" & Col_value &
l_lastrow & ", txt1)")
Worksheets("S2").Activate
Range("A1").Value = l_ans



Worksheet s1 {source data ws}

A
1 txt1
2 txt1
3 txt2

Worksheet s2 {results ws}

A
1 0
2
3

 

[Lionel H]

Hi miek,
replace:
l_ans = ActiveSheet.Evaluate("COUNTIF(" & Col_value & "1:" & Col_value &
l_lastrow & ", txt1)")
by:
l_ans = ActiveSheet.Evaluate("COUNTIF(" & Col_value & "1:" & Col_value &
l_lastrow & ", ""txt1"")")

regards,
Lionel

 

[miek]

Ok that worked. however, If I replace ""txt1"" with a varible

s_in as string
s_in = "txt1"
l_ans = ActiveSheet.Evaluate("COUNTIF(" & Col_value & "1:" & Col_value &
l_lastrow & ", s_in)")

It still always returns a zero... what gives?

 

[Lionel H]

I've not tested this, but I would expect the following to work:

l_ans = ActiveSheet.Evaluate("COUNTIF(" & Col_value & "1:" & Col_value &
l_lastrow & "," & s_in & ")")

 

[Bob Phillips]

l_ans = ActiveSheet.Evaluate("COUNTIF(" & Col_value & "1:" & Col_value &
l_lastrow & "," & s_in & ")")

 

[Lionel H]

Which only goes to show you should test things first.
you also need to change
s_in = "txt1" to
s_in = """txt1"""

regards
L

 

[miek]

Thanks I'm in the clear.......for now!

Which only goes to show you should test things first.
you also need to change
s_in = "txt1" to
s_in = """txt1"""

regards
L