count unique values in a col
 

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" wrote:

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" wrote:

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

count unique values in a col
 

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 & ")")


"miek" wrote:

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" wrote:

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" wrote:

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

count unique values in a col
 

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


--
__________________________________
HTH

Bob

"miek" wrote in message
...
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" wrote:

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" wrote:

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

count unique values in a col
 

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

regards
L

"Lionel H" wrote:

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 & ")")


"miek" wrote:

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" wrote:

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" wrote:

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

count unique values in a col
 

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

"Lionel H" wrote:

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

regards
L

"Lionel H" wrote:

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 & ")")


"miek" wrote:

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" wrote:

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" wrote:

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