Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-16-2016, 10:02 AM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default Need to improve speed of Excel macro that searches for whole word matches

Iíve constructed macros that search a user range on a worksheet and looks for whole word matches (one provides case sensitive and the other insensitive searches with the option compare statements). It extracts the matches it finds to a new workbook. It determines the whole word match by testing whether the search text in the range is preceded or followed by a non-letter or non-number.



It runs very well if there are a small number of hits, even if the search range is several thousand rows, but slows down if it finds a lot of hits, probably because it loops through the ASC characters for the front and back of the search hit. For example, with a range of about 5800 rows of data, it took just over 2 minutes to extract 455 hits. But searching the same range for a different criterion that had only 14 hits took around 15 seconds.

Does anyone have a suggestion for speeding this code up when the hit count is high? Macro is attached in text file.
Attached Files
File Type: txt Find_Whole_Word_CS.txt (3.4 KB, 7 views)
Reply With Quote
  #2  
Old 02-16-2016, 10:31 AM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

I'd look into the use of range.Find and .FindNext methods.
Reply With Quote
  #3  
Old 02-16-2016, 12:47 PM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default

I'd be happy to do that but am not VBA fluent enough to change the code to apply those methods. As it is, I had to test and try to rewrite other match whole word modules, only to find they didn't quite do what I wanted them to do. The one I have now is the only one that actually worked (without doing a find and replace).

Can you suggest what the code should look like using range.find and .findnext?
Reply With Quote
  #4  
Old 02-16-2016, 02:22 PM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

As we don't know what you're actually working with, how about you supply a sample workbook that what you have now works on and can be used to make sure anything suggested will work on.
Reply With Quote
  #5  
Old 02-17-2016, 01:01 PM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default Need to improve speed of Excel macro that searches for whole word matches

That data is private but I have created a similar database of about 1070 records with the 4 code modules (two for case sensitive and two for case insensitive; the other two are for finding all occurrences and for whole word matches).

The code ran pretty fast on the 1070-record database, so I copied most of it 6 times to increase it to about 6300 records. Results running match whole word, case insensitive on a search for 'the':

searching 1070 records: found 462 in 25 seconds
searching 6319 records: found 2717 in 49 seconds

These were faster times than my original tests but I would still like to know if the searching can be faster, whether using find.next or another method.

Sample database attached.
Attached Files
File Type: xlsm Sample File for Extract by Row macro.xlsm (276.2 KB, 9 views)
Reply With Quote
  #6  
Old 02-17-2016, 06:24 PM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

The find-findnext was only a suggestion of something to look into.
Don't know if this is of any use or not.

Here is what I did:
added module2, altered the forms' Search and Extract procedure a little... it's commented.
put a timer in your Find_Whole_Word_CIS1(x) macro.

In it's original state, on my computer, that macro ran in 48 seconds (last input box to first msgbox).

Adding Application.ScreenUpdating=False reduced the time to 9 seconds.

Using .Find and .FindNext (in module2) the time to run was 3 seconds, but the macro is questionable, as the results didn't jive.

The original macro found 2717 records.
60 of those weren't in the results of find/findnext.
I can account for that being 'the' at the beginning of a sentence or first word inside brackets and not having a preceding space.

The find/findnext macro found 3018 records.
361 of these aren't in the original results and I don't know what gives with that.
The attached Comparison workbook contains only the results of the 2 macros, the discrepancies are highlighted.
Attached Files
File Type: xlsm Comparison.xlsm (225.1 KB, 11 views)
File Type: xlsm Sample File for Extract by Row macro.xlsm (277.6 KB, 7 views)
Reply With Quote
  #7  
Old 02-17-2016, 07:15 PM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default

Thanks for checking this. Just after sending that sample file, I ran the match whole word macro against my real database on one word. It found 89 matches. When I ran the original all occurrences code, it found 260. The search text would have been a single word in all cases, so there must be a flaw in the match whole word code. It is not due to the search text being the only text in any cell because some of the hits were such cells. I had an earlier version of the match whole word code that allowed for that by testing only the start of the word to see if it had any spaces or punctuation. But when i tested it, it made no difference. I just tested that earlier version and also got 89 hits.

I like your 'Identify_Missing' macro - that's really helpful. But I'm at a loss as to why the match whole word misses so many rows. The speed is less of an issue than the accuracy; it's not worthwhile keeping if it misses qualified rows.

Do you have ideas as to why, or suggestions for different code that finds all whole word matches?
Reply With Quote
  #8  
Old 02-17-2016, 08:56 PM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

No question, accuracy must come first.

The reason so many rows are being skipped is only the first occurrence of 'the' is being looked at:
seq 23: Antikythera
seq29: no space between writes and the
seq 108 and 109: Southern Ohio
etc.
etc.

Just so this can be looked at with a totally open (maybe that should be blank) mind, can you state what the original request and requirements were regarding this ?
Reply With Quote
  #9  
Old 02-17-2016, 09:18 PM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default

The original request was my own doing. I had code that extracted text I searched regardless of case and matching whole words (I adapted it from http://stackoverflow.com/questions/2...n-one-workbook). I usually got rows that I didn't want just because the code wasn't matching, so I wondered about changing it to match the whole word. Not being a VBA guru, I had to look for examples that I could maybe adapt for my purpose.

I did just comment out two lines of code: the 'If b < 48 Or a > 57 Then' and the 'If a < 48 Or b > 57 Then' which I had added so the code would include numbers adjacent to the search text when it was looking to determine if the whole word was a match. When I ran that code, it found the same number of hits as the original code above searching for a word that I knew was a whole word in all 260 occurrences. With that b < 48 Or a > 57 code uncommented, I got 89 hits on the same search term. That is great - but if I added a number to the search term in one of the rows, the code doesn't recognize it, whether or not the b < 48 Or a > 57 lines are there. So the way it is now, I think the only reason lines will be skipped is if the search term is either preceded or followed by a number (without a space). I guess I can live with that. In the example you mention (seq 23: Antikythera, seq29: no space between writes and the seq 108 and 109: Southern Ohio), 'the' is not a whole word, so on a whole word match run, I don't want those rows. Only if I choose "all occurrences" would I want to see 'the'.

If you can suggest a way to have the code see numbers without compromising the other (whole word match) searching, I''d be happy to hear it!
Reply With Quote
  #10  
Old 02-17-2016, 10:15 PM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
seq 108 and 109: Southern Ohio), 'the' is not a whole word, so on a whole word match run, I don't want those rows.
Yes you do... further on, each line has the stand alone whole word you're looking for.

Is your search always confined to a single column ?
Reply With Quote
  #11  
Old 02-18-2016, 08:20 AM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Here's a different approach.

Started with the sample workbook you posted.
Kept your sheet of data and userform.
For each cell in the TestRng, split the cell contents into an array of individual words as separated by spaces.
Checks for array elements being same as SearchText.

Using the same 'the' as what to check for
takes about 10 seconds and returns 3090 records case insensitive and 2814 records case sensitive.

Appears to me your challenge will be determining how many records should be returned in order to know if anything works correctly.
Attached Files
File Type: xlsm Extract_Rows_containing_word.xlsm (267.9 KB, 12 views)
Reply With Quote
  #12  
Old 02-18-2016, 10:51 AM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default

This looks really good - thank you. The only issue I have with it is that some search words might be preceded or followed by punctuation such as a dash, question mark, comma, etc.

I am a little familiar with the regex method in "[^A-Z0-9 ]", but when I tried adding the punctuation to it, the code ignored it. So the code does find and extract all whole word matches unless the search text has anything but a letter or number immediately in front of or behind it. How do I add the punctuation so the code recognizes the hits as whole word matches?

Regarding the range extending beyond one column, yes, I did not put a restriction on that because I wanted the search to look in all selected columns and rows (and that works fine, as far as I've seen). And you're right about there being more than one instance of the searchtext in any row, so that if one does not match the whole word and another one does, the code should extract it. I was responding only to the text you posted for illustration.
Reply With Quote
  #13  
Old 02-18-2016, 11:37 AM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

I'm sure if you play around with the stuff in the UDF it will remove anything and everything you could possibly think of.
Google VBA to remove punctuation special characters and numbers, you'll be able to adapt something.
Reply With Quote
  #14  
Old 02-18-2016, 02:00 PM
Marrick13 Marrick13 is offline Need to improve speed of Excel macro that searches for whole word matches Windows XP Need to improve speed of Excel macro that searches for whole word matches Office 2010 32bit
Competent Performer
Need to improve speed of Excel macro that searches for whole word matches
 
Join Date: Jun 2006
Posts: 102
Marrick13 will become famous soon enough
Default

Well, I've just spent several hours trying various combinations of punctuation and special characters and none of them has had any effect. The latest I've tried are:

.Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!" & _
"#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:" & _
"[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:" & _
"[a-z0-9-]*[a-z0-9])?"

and

.Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~- ]"

I don't know how else to tell the code to identify hits as whole word matches if they adjoin these characters. Am I getting warm or still cold here?
Reply With Quote
  #15  
Old 02-18-2016, 05:58 PM
NoSparks NoSparks is offline Need to improve speed of Excel macro that searches for whole word matches Windows 7 64bit Need to improve speed of Excel macro that searches for whole word matches Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 829
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

I believe the .Pattern thing works opposite to your line of thought.

.Pattern = "[^characters you want to keep]"

.Pattern = "[^A-Za-z0-9\ ]"
keeps upper case A thru Z
keeps lower case a thru z
keeps numerals 0 thru 9
don't know what the back slash does as back slashes are removed
keeps spaces

I add a row to the top of your data and put in this

.Pattern = "[A-Z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!" & "#$%&'*+/=\?^_`{|}~-]+)*@(?:[a-z0-9](?:" & _"[a-z0-9-]*[a-\z0-9])?\.)+[a-z0-9](?:" & _"[a-z0-9-]*[a-z0-9])?"

use debug.print and this is what it gets reduced to using .Pattern = "[^A-Za-z0-9\ ]"

Pattern AZ09az09 az09 az09az09az09 az09az09
Reply With Quote
Reply

Tags
case, search, whole word match

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to improve speed of Excel macro that searches for whole word matches how to speed up macro AC PORTA VIA Excel 3 10-30-2015 08:58 PM
Excel formula required to speed up process of creating species rarefaction curves. Ecology! Tim_F Excel 2 07-18-2014 04:56 AM
Need to improve speed of Excel macro that searches for whole word matches word and keyboard speed ptz Word 2 09-20-2012 06:37 AM
Need to improve speed of Excel macro that searches for whole word matches Help -can i improve this way of working? lucky67 Office 1 01-26-2012 09:20 PM
Need to improve speed of Excel macro that searches for whole word matches Change how windows searches cadams Office 1 12-07-2010 10:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft