xlValues vs xlFormulas

[jstevens]

I don't understand why the search works when I use "xlFormulas" but not "xlValues". Row three contains values no formulas. The code searches Row 3 for the search criteria.

Code: Select all

Sub TestIt()

strSearch = "Source"

With Sheets("Sheet1").Rows("3:3")
    'Set c = .Find(strSearch, LookIn:=xlValues)        'Does not work
    Set c = .Find(strSearch, LookIn:=xlFormulas)       'Works
    
    If Not c Is Nothing Then
        Msgbox "Found in Cell " & c.Address
        
    Else
        Msgbox "not Found"
    End If

End With

End Sub

Regards,
John

[HansV]

I can't reproduce the problem; could you attach a sample workbook? Thanks!

[jstevens]

Hans,

I have attached a sample file.

Sample.xlsm

Regards,
John

[HansV]

If I uncomment the line

'Set c = .Find(strSearch, LookIn:=xlValues)

and comment out the line

Set c = .Find(strSearch, LookIn:=xlFormulas)

the code still finds the string in cell A3. Have you tried it in your sample workbook?

[jstevens]

Hans,

I did test on both prior to posting the sample file and the xlValues did not find the string in cell A3. I am using Excel 2007.

I just tried it with Excel 2010 and it worked just fine. It appears to be an Excel 2007 challenge.

Thanks for taking a look,
John

[HansV]

That must be it. Excel 2007 is a failure in so many ways...

[chamdan]

Hans,

By curiosity, I wanted to see what would happen since I have Excel 2007 and it worked perfectly. Here below is the result:
[img]

MWSnap143.jpg

[/img]

Chuck

[HansV]

Thanks for testing. I wonder why it doesn't work for John Stevens...