Force VBA countif to find string, not value
 

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

Force VBA countif to find string, not value
 

On Sun, 02 Sep 2007 16:23:23 -0700, c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

Not as fast as COUNTIF, but it should work:

For Each c In Range("a:a")
If c.Text = "010020" Then


--ron

Force VBA countif to find string, not value
 

On Sun, 02 Sep 2007 20:39:10 -0400, Ron Rosenfeld
wrote:

On Sun, 02 Sep 2007 16:23:23 -0700, c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

Not as fast as COUNTIF, but it should work:

For Each c In Range("a:a")
If c.Text = "010020" Then


--ron

Or, as was mentioned in another thread by a different Ron (Coderre)

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020*") 0 Then


--ron

Force VBA countif to find string, not value
 

On Sun, 02 Sep 2007 21:46:19 -0400, Ron Rosenfeld
wrote:

On Sun, 02 Sep 2007 20:39:10 -0400, Ron Rosenfeld
wrote:

On Sun, 02 Sep 2007 16:23:23 -0700, c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

Not as fast as COUNTIF, but it should work:

For Each c In Range("a:a")
If c.Text = "010020" Then


--ron

Or, as was mentioned in another thread by a different Ron (Coderre)

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020*") 0 Then


--ron

And see Ron Coderre's critique of this method as it can match 010020a.
--ron

Force VBA countif to find string, not value
 

If you really want VBA, you could use something like:

If ActiveSheet.Evaluate("SumProduct(--(""a1:a100""=""010020""))") 0 then

=sumproduct() can't use the entire column until xl2007.



c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

--

Dave Peterson

Force VBA countif to find string, not value
 

Thanks, but I have multiple sheets of about 60k records each, so need
a fast method, and the wildcard won't work because I have similar
prefixed items.

On Sep 2, 9:19 pm, Ron Rosenfeld wrote:
On Sun, 02 Sep 2007 21:46:19 -0400, Ron Rosenfeld
wrote:

Not as fast as COUNTIF, but it should work:

For Each c In Range("a:a")
If c.Text = "010020" Then

Or, as was mentioned in another thread by a different Ron (Coderre)

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020*") 0 Then

And see Ron Coderre's critique of this method as it can match 010020a.

Force VBA countif to find string, not value
 

That works for that lookup value, which is text. But it won't work
when it's a value.

In my list (60k), some are text and some are values. (It's how it came
in from another program.) What looks like numbers, Excel set as
numbers and what looked like text became text. So, now when I run a
routine matching a string from one array to another, it fails because
sometimes it can be a value and other times it's text. I can't figure
out how to write the syntax to work in both cases.

Specifically, how do you handle a countif or match when the variable
criteria might be looking up either value or text. I add quotes for
text, it fails on numbers. I remove quotes and if fails on text.

How do you deal with this? Is there some way to set my variable as a
certain object type to do this?



On Sep 2, 9:41 pm, Dave Peterson wrote:
If you really want VBA, you could use something like:

If ActiveSheet.Evaluate("SumProduct(--(""a1:a100""=""010020""))") 0 then

=sumproduct() can't use the entire column until xl2007.





c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Force VBA countif to find string, not value
 

On Sun, 02 Sep 2007 20:20:39 -0700, c mateland wrote:

Thanks, but I have multiple sheets of about 60k records each, so need
a fast method, and the wildcard won't work because I have similar
prefixed items.

On Sep 2, 9:19 pm, Ron Rosenfeld wrote:
On Sun, 02 Sep 2007 21:46:19 -0400, Ron Rosenfeld
wrote:

Not as fast as COUNTIF, but it should work:

For Each c In Range("a:a")
If c.Text = "010020" Then

Or, as was mentioned in another thread by a different Ron (Coderre)

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020*") 0 Then

And see Ron Coderre's critique of this method as it can match 010020a.


OK, here is a VBA routine that I believe will work for what you want:

Evaluate("sumproduct(--exact(a1:a60000,""010020""))")

It matches 010020 but does not match 10020.

So, in your context:

If Evaluate("sumproduct(--exact(a1:a60000,""010020""))") 0 Then
--ron

Force VBA countif to find string, not value
 

First, I had a typo in my expression.

It should have been:
If ActiveSheet.Evaluate("SumProduct(--(a1:a100=""010020""))") 0 then

But couldn't you check twice?

Dim myVal As Variant

myVal = "123" '123"

If ActiveSheet.Evaluate("SumProduct(--(a1:a100=""" & myVal & """))") 0 _
or ActiveSheet.Evaluate("SumProduct(--(a1:a100=" & myVal & "))") 0 Then



c mateland wrote:

That works for that lookup value, which is text. But it won't work
when it's a value.

In my list (60k), some are text and some are values. (It's how it came
in from another program.) What looks like numbers, Excel set as
numbers and what looked like text became text. So, now when I run a
routine matching a string from one array to another, it fails because
sometimes it can be a value and other times it's text. I can't figure
out how to write the syntax to work in both cases.

Specifically, how do you handle a countif or match when the variable
criteria might be looking up either value or text. I add quotes for
text, it fails on numbers. I remove quotes and if fails on text.

How do you deal with this? Is there some way to set my variable as a
certain object type to do this?

On Sep 2, 9:41 pm, Dave Peterson wrote:
If you really want VBA, you could use something like:

If ActiveSheet.Evaluate("SumProduct(--(""a1:a100""=""010020""))") 0 then

=sumproduct() can't use the entire column until xl2007.





c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Force VBA countif to find string, not value
 

Can you convert the text looking cells to numbers first?

copy an empty cell
select the range to change from text numbers to number numbers
edit|paste special|check add

Then you'd always be looking for numbers.

(and you could do that paste special stuff in your code, too.)

c mateland wrote:

That works for that lookup value, which is text. But it won't work
when it's a value.

In my list (60k), some are text and some are values. (It's how it came
in from another program.) What looks like numbers, Excel set as
numbers and what looked like text became text. So, now when I run a
routine matching a string from one array to another, it fails because
sometimes it can be a value and other times it's text. I can't figure
out how to write the syntax to work in both cases.

Specifically, how do you handle a countif or match when the variable
criteria might be looking up either value or text. I add quotes for
text, it fails on numbers. I remove quotes and if fails on text.

How do you deal with this? Is there some way to set my variable as a
certain object type to do this?

On Sep 2, 9:41 pm, Dave Peterson wrote:
If you really want VBA, you could use something like:

If ActiveSheet.Evaluate("SumProduct(--(""a1:a100""=""010020""))") 0 then

=sumproduct() can't use the entire column until xl2007.





c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Force VBA countif to find string, not value
 

This seems to be about searching for text and functions interpreting
them into numbers.

To elaborate more specifically, somehow I need the routine to check if
the item from the first array is present in the second array, and if
so, which row is it on? Both arrays are text, but some can look like
values (i.e., 150, 0150) and others look only like text (i.e., T150,
150A).

Here's a snippet of what I'm doing now...

'cItemArray1 = lookup value from first array (looping all such items)

If Application.WorksheetFunction _
.CountIf(Range("a:a"), cItemArray1) = 0 Then 'not found

'code to record cItemArray1 as not found (exception)

Else 'countif found the item in array2...

'note row number of match in Array2
iMatchRow = Application.WorksheetFunction _
.Match(cItemArray1, Range("a:a"), 0)

When cItemArray1 = "010020" the countif finds 10020 in Array2, which
is incorrect. It then passes to the match function, which cannot find
010020 because it searches for the literal string, whereas the countif
interprets. The match function then throws an error, because the
countif showed the lookup as existing when it really didn't.

(I use the countif before the match because I can't figure out a way
to do an error handler for a match for a non-existant lookup - any
suggestions?)

I tried your code, but I get a type mismatch error when checking
twice. That's when a text string, which cannot be a number, is my
lookup. I'm sure it's choking on the syntax that removes the quotes.

As far as first converting the arrays to values, I added a routine
that first converts both arrays to text, so there's not a mix of
values and text. However, I can't convert them to numbers because
00569 will become 569, which will hose everything. My items, for
example, include 00569, 0569, 569, 00569T, and 569T.

Thanks for any advice.



On Sep 3, 7:47 am, Dave Peterson wrote:
First, I had a typo in my expression.

It should have been:
If ActiveSheet.Evaluate("SumProduct(--(a1:a100=""010020""))") 0 then

But couldn't you check twice?

Dim myVal As Variant

myVal = "123" '123"

If ActiveSheet.Evaluate("SumProduct(--(a1:a100=""" & myVal & """))") 0 _
or ActiveSheet.Evaluate("SumProduct(--(a1:a100=" & myVal & "))") 0 Then





c mateland wrote:

That works for that lookup value, which is text. But it won't work
when it's a value.

In my list (60k), some are text and some are values. (It's how it came
in from another program.) What looks like numbers, Excel set as
numbers and what looked like text became text. So, now when I run a
routine matching a string from one array to another, it fails because
sometimes it can be a value and other times it's text. I can't figure
out how to write the syntax to work in both cases.

Specifically, how do you handle a countif or match when the variable
criteria might be looking up either value or text. I add quotes for
text, it fails on numbers. I remove quotes and if fails on text.

How do you deal with this? Is there some way to set my variable as a
certain object type to do this?

On Sep 2, 9:41 pm, Dave Peterson wrote:
If you really want VBA, you could use something like:

If ActiveSheet.Evaluate("SumProduct(--(""a1:a100""=""010020""))") 0 then

=sumproduct() can't use the entire column until xl2007.

c mateland wrote:

Excel 2003

In a VBA routine, I'm trying to do a simple countif.

If Application.WorksheetFunction _
.CountIf(Range("a:a"),"010020") 0 Then

This evaluates to TRUE when I expect FALSE.

In the column it's evaluating, I have...

A
10020
10021
10022

It's obviously counting the 10020 entry, but I want an exact match to
the literal criteria I enter.

How do I force the countif to look for the text string, not the value?

Thanks,
Chuck

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Force VBA countif to find string, not value
 

A simple error trap seems to be working now with a match that cannot
resolve. Since the match is more reliable than the countif, I'll test
using the match with error trapping instead.



On Sep 3, 1:58 pm, c mateland wrote:
(I use the countif before the match because I can't figure out a way
to do an error handler for a match for a non-existant lookup - any
suggestions?)

Force VBA countif to find string, not value
 

And if you want the row number, application.match makes a lot more sense.

c mateland wrote:

A simple error trap seems to be working now with a match that cannot
resolve. Since the match is more reliable than the countif, I'll test
using the match with error trapping instead.

On Sep 3, 1:58 pm, c mateland wrote:
(I use the countif before the match because I can't figure out a way
to do an error handler for a match for a non-existant lookup - any
suggestions?)

--

Dave Peterson

Force VBA countif to find string, not value
 

But, error trapping application.match wasn't working... until I found
the IsError(Application.Match method. That seems to work a lot better.



On Sep 3, 2:47 pm, Dave Peterson wrote:
And if you want the row number, application.match makes a lot more sense.

c mateland wrote:

A simple error trap seems to be working now with a match that cannot
resolve. Since the match is more reliable than the countif, I'll test
using the match with error trapping instead.

On Sep 3, 1:58 pm, c mateland wrote:
(I use the countif before the match because I can't figure out a way
to do an error handler for a match for a non-existant lookup - any
suggestions?)

--

Dave Peterson