 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007Force 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 >Excel 2003 > [quoted text clipped - 19 lines] >Thanks, >Chuck Not as fast as COUNTIF, but it should work: For Each c In Range("a:a") If c.Text = "010020" Then --ron >>Excel 2003 >> [quoted text clipped - 26 lines] > >--ron Or, as was mentioned in another thread by a different Ron (Coderre) If Application.WorksheetFunction _ .CountIf(Range("a:a"),"010020*") > 0 Then --ron >>>Excel 2003 >>> [quoted text clipped - 33 lines] > >--ron And see Ron Coderre's critique of this method as it can match 010020a. --ron 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. > >>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. >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 [quoted text clipped - 11 lines] > >> 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 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. > Excel 2003 > [quoted text clipped - 19 lines] > Thanks, > Chuck  SignatureDave Peterson 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? > If you really want VBA, you could use something like: > [quoted text clipped - 31 lines] > > - Show quoted text - 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 > That works for that lookup value, which is text. But it won't work > when it's a value. [quoted text clipped - 48 lines] > > > > - Show quoted text - SignatureDave Peterson 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. > First, I had a typo in my expression. > [quoted text clipped - 68 lines] > > - Show quoted text - 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. > (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?) And if you want the row number, application.match makes a lot more sense. > 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 [quoted text clipped - 3 lines] > > to do an error handler for a match for a non-existant lookup - any > > suggestions?) SignatureDave Peterson But, error trapping application.match wasn't working... until I found the IsError(Application.Match method. That seems to work a lot better. > And if you want the row number, application.match makes a lot more sense. > [quoted text clipped - 9 lines] > > 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.) > That works for that lookup value, which is text. But it won't work > when it's a value. [quoted text clipped - 48 lines] > > > > - Show quoted text - SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.