".Find" arguments

**Phil_V** · 08-03-2009, 07:50 AM

I have generally used the .Find function in the following form:

Please Login or Register  to view this content.

As I figured that I wanted to look at the VALUE of the Excel cell when finding, not the text fo the formula that created that value.

However today I had a situation where I just could not find a value that I KNEW was there.
As I hadn't changed much i did a small test to repeat the 'issue'.

In cell A1 I entered;

Please Login or Register  to view this content.

Fitted the column to the data and then used the following code to find it:

Please Login or Register  to view this content.

It found it fine.
I then resized the column and the cell changed to "9.9E+08", ran the macro, and not found.
I resized it smaller still until the cell changed to "##", ran the macro, and not found.

I can only take from this that the .Find function when set to xlValues doesn't find the values in the spreadsheet, but actually the values DISPLAYED in the cells, (which now that I've tested it seems to be the same as the behaviour of the 'Find / Replace' dialog.

Does this mean that every time I do a find and I want the actual value of the cell, (not the displayed value), I should be search for xlFormulas? Do I then also have to test the first character of every found cell to make sure it is not "=", so that I know I have actually found a VALUE rather than a FORMULA ?

Thanks

**Richard Schollar** · 08-03-2009, 08:32 AM

Hi Phil

It certainly looks like the Find uses what would be the Text property of the cell (in VBA) rather than the actual value.

If you know in advance you only want to find cells containing "XX" that are formula cells/are not formula cells, you could use the SpecialCells method to return a range that only contains formulas or values. Alternatively, you could check the HasFormula property of the returned range object to determine if it contained a formula.

Richard

**StephenR** · 08-03-2009, 08:49 AM

Never noticed that. I generally use Formulas rather than Values. As a rule I think it's better practice to specify all parameters.

**Phil_V** · 08-03-2009, 09:33 AM

Thanks both.

Stephen, just regarding your comment, am I correct in thinking that even if I DID specify all the arguments it wouldn't effect anything here? (I can't see any of the optional arguments that would effect it?)

Richard, I think I shall have to start using one of the two methods that you have suggested.

I may have 987654321 in a cell which is too narrow, (which is the cell I want to find), and also have somewhere else on the sheet =987654321+1 which I do not want to find.
If I search for xlPart, xlFormulas, and "98765" for example, I will find both cells, so will definitly have to use one of the 2 "make sure it's not a formula" methods you mentioned

**StephenR** · 08-03-2009, 10:28 AM

Phil - yes, I think in this case you are correct to think that. The thinking more generally is that code may not work because (unbeknownst to or forgotten by the coder) some of the settings may have been altered, say directly from the worksheet.