Excel VBA Code to find two words near each other (proximity search)

Hello -

I am having a difficulty coming up with a macro that would do all of the following:
1. Search a column of texts where two words are near each other regardless of order (e.g., within 5 words of each other)
2. Highlight those cells
3. Variations of the same word (e.g., charge, charges, charged, charging) needs to be taken into account

I am relatively new to using VBA, so I appreciate any help or insight!

Example: I want to find and highlight the cells in a column where the word "incorrect" is within 3 words of "bill"
1. The vendor billed the company incorrectly. -> this cell would be highlighted
2. The vendor sent an incorrect bill to the company. -> this cell would be highlighted
3. The vendor did not send a bill to the company. -> this cell would not be highlighted

I was thinking to use Regular Expression. Any thoughts?

Try this macro. I'm assuming your data is in column A. Change the columns letters (in red) to suit your needs.

Please Login or Register  to view this content.

Edit:

Do not pay attention, mistake ..

Originally Posted by demiwen12

Cell A1 (text): "The vendor billed the company incorrectly."
Cell B1 (summary count): 2 -> function to call the code that tallies the total hits

B1:
=SummaryCount(A1)

Please Login or Register  to view this content.

Basically I have a set of search terms that may be in a different format from the one I provided. My list, which has about 15 terms, is finalized, so it is unlikely that there will be additional terms. Examples of some of my terms look like the below; hence, the reason why I tried to edit the code the way I did since the pattern is not consistent and can be quite complex. My plan was to write out these terms in regular expressions, paste them in the "Pattern" column, and then just reference this column when I count the matches (referring to the workbook previously attached).

- incorrect* w/3 bill*
- (reclass* OR charg* OR edit*) w/5 (time OR hour*)
- actual w/3 (time OR hour*)
- budget*

Sorry about the back and forth!

[Edit] Also just want to note that I've got the pattern written out for all my terms in regular expressions and when I run the searches, they do work. Just need to figure out count!

Originally Posted by demiwen12

[Edit] Also just want to note that I've got the pattern written out for all my terms in regular expressions and when I run the searches, they do work. Just need to figure out count!

Are you sure?
When you use asterisk * or other meta-characters in the pattern, you will need to escape them, if you do a LIKE comparison, existence of pre/suf fix will also be the matter.

Anyway, if you know how to create the pattern, you don't need any help...

BTW, don't place "ON ERROR" statement in the first line, you will not find the cause of the error...

P.S

Originally Posted by demiwen12

Please Login or Register  to view this content.

There is no Rows.Count, just Count, and your function will return 1 or 0, nothing else...