Using the Find method is the code version of pressing Ctrl-F in Excel and then typing in a word to search for - LookIn:=xlValues tells Excel to search in the values, rather than in the formula, and Lookat:=xlWhole says that the entire cell has to match the sWordToMatch, not just part of it. So searching for "food" wouldn't match "foodstuff", for example.
What Find returns is a range object, not a variable in the normal sense, but something we can treat as a range. So if we wanted we could put in a line of code saying:
And the macro would highlight in red each match found.
The second line of code you quoted checks that a range has been returned. If there is no match for the search term then rngMatchWord will be recorded as Nothing, and if there is no match then we do not need to continue.
My code calls the Find method in a loop to get every match, but as with Ctrl-F it will loop back around to the beginning once it has found all of the matches. To stop it doing that we record the cell address of the first match, e.g. $B$4 and stop looping when we get back round to that address.
So, what's in the rngMatchWord variable? Lots of things, we could examine rngMatchWord.Value if we wanted to see what value it has, but that value will be the one we just searched for, so what's important is not what's in rngMatchWord, but where that range is, because we know it's one cell to the right of a value we want to count, so rngMatchWord.Offset(0,-1).Value gets the count for us to add to our running total.
If you're new to coding, or used to more old-fashioned languages, it can take a while to get your head around these object variables, but they are incredibly powerful and can make coding a lot easier.
Bookmarks