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
Hi,
I have need of a VBA ‘Find All’ function. I’ve tried Chip Pearson’s (http://www.cpearson.com/Zips/modFindAll.zip and http://www.cpearson.com/excel/findall.aspx) but I seem to be breaking it; and I think this is due to the fact that I’m passing it a range object, but that range might contain discontinuous ranges (i.e. A1:A6,C2:C4 etc).
Is this something that your version of FindAll can handle, or would it be hard to do so?
Perhaps I’m better off passing each .Area of my discontinuous SearchRange to either your’s or Chip’s function?
Thanks very much for the effort that goes into these bits of code and publishing them for free, they’re life savers; I was terrifed when I discovered how hard it is to get the results you expect from VBA’s .Find!
Tushar,
Possibly a lengthy post.
I started to comment on your site, but couldn’t capture the capcha.
I have a situation w/ this and would appreciate thoughts.
I think it might be a string length problem, but char length of result is 253 v. 255/256, which appears odd to me.
I’m using your example: ‘show the address of the range in the activesheet that contains a value of 1 (modified for my own use such as looking for text v. number, etc.)
To retest, I created a worksheet w/ 72 cases of “DDoE” and copied the code from your site.
Worksheet.Used Range is $C$25:$EE$150, w/ text organized but scattered throughout. As an example: there are six occurrences in column C, six occurrences in column F, the same for columns I, P, AA, BB, etc., however the row spacing is different for each column.
(don’t ask. My workheet has functional value, but mainly used as a playground)
I am only getting 37 of the 72 occurrences reported back to me.
For testing, I modified your UDF do loop, to output each CurrCell.Address and output the FindAll.Address at the end.
Each CurrCell.Address appears to be correct, the object FindAll also appears to be correct, but the reported result is not.
Would appreciate thoughts and comments.
I would also like to say Thank You to you, Dick, and all the others for the work and thought put into these posts.
for further clarification re last post: row spacing is different for each column
In column C, occurrences might be five rows apart, in column F ten rows apart, column I might be 16 rows apart, column P might be 22 rows apart, etc.
Again, thanks.
I have found http://cpearson.com/EXCEL/FINDALL.ASPX , but have never been able to make it work?
Looking for string cell A2 in my Table $A$1:$P$599 in sheet 2, I have naively written
=findall(A2,Sheet2!A1:P599) assuming that was its required parameters. But it just return #value! all the time.
What am I doing wrong?
Molly: Reverse the arguments.
=FindAll(Sheet2!A1:P599,A2)