Force VBA countif to find string, not value

[c mateland]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Dave Peterson]

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]

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.

 

[c mateland]

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?

 

[Ron Rosenfeld]

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.

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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]

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.

 

[c mateland]

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.

 

[Dave Peterson]

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

 

[c mateland]

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