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

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

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

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

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


--
__________________________________
HTH

Bob

"miek" <(E-Mail Removed)> wrote in message
news:644D7763-A4DA-4367-ABB3-(E-Mail Removed)...
> 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

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

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