".Find" arguments

[Phil_V]

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

Set found = .Columns(1).Find(what:="987654321", LookIn:=xlValues, lookat:=xlWhole)

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;

987654321

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

Sub Test_find()
Dim found As Range
    With Worksheets(1)
        Set found = Nothing
        Set found = .Columns(1).Find(what:="987654321", LookIn:=xlFormulas, lookat:=xlWhole)
        If found Is Nothing Then
            MsgBox "Not Found"
        Else
            MsgBox "Found at: " & found.Address
        End If
    End With
End Sub

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