The Range.Find method and a FindAll function
Two things that could be better about the Range.Find method have been 1) up-to-date and correct documentation, and 2) adding the UI’s ‘Find All’ capability to the Excel Object Model. As of Office 2013 neither has happened.
Consequently, every time I want to use the Find method, I continue to have to jump through hoops to figure out the correct values for the different arguments.
I also discovered that FindNext does not work as expected when one wants to search for cells that meet certain format criteria. Consequently, I updated my long available FindAll function so that it works correctly with format criteria.
For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1001%20range.find%20and%20findall.shtml
For the Range.Find() lookin parameter, you can also pass in xlComments which is missing in your notes. See the XlFindLookIn class in the object browser.
Tushar,
Speaking of documentation, I am definitely unhappy with Microsoft’s poor VBA documentaion for Office 2013 in general.
The Excel 2013 developer site was set up on July 16, 2012.
http://msdn.microsoft.com/en-us/library/office/ee861528.aspx
At this site, there resides the Object model VBA programming language. All of the new objects, properties and methods are listed. And, there are ZERO CODE EXAMPLES that have been added since it was created. Since Microsoft is now selling this product, you would think that it is about time to show people that might be interested some examples of how to effectively use the new objects, properties and methods. I repeat, there has been NO updates to this site since it was created, and when you click HELP! in Excel 2013, this is where you are sent. It’s sort of like buying a fully-loaded Mercedes-Benz that comes with no owners manual.
Sorry for the rant on your nice post.
David Hager, a former Excel MVP