| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I count unique values | Excel Worksheet Functions | |||
| Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
| Count Unique Values | Excel Worksheet Functions | |||
| How to count unique values? | Excel Worksheet Functions | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
Hybrid Mode
