Cell Format breaking Find method

[MrKowz]

This isn't a particular issue I need to solve, but moreso something I'm trying to research and not finding much on a handful of Google searches.

Background - I recently helped a user debug an issue with some code and tracked it down to the number formatting of some cells. The user had the "Accounting" number format chosen for cells which contained string data ("Yes"/"No"). What I've found is that neither the VBA Find method (set rng = .find(value, lookin:=xlvalues, lookat:=xlwhole)) or using CTRL+F is able to find the "Yes" which has that format chosen.

Upon further testing, I've been able to replicate the issue and also extend it to any formatting which includes errant spaces before/after the @. For example, the formats " @" and "@ " both yielded the same results - the find method and ctrl+f couldn't find the values.

Just wondering if anyone else has seen this before and if this is just an accepted bug or anything. It really struck me as bizzare since I was always under the impression that cell values were not impacted in any way by cell formatting.

 

[Marcelo Branco]

It worked for me with
LookAt:=xlPart
Try it

M.

 

[JoeMo]

I can't reproduce that using ctrl+F in Excel 2010. Does your Find & Replace dialog box have the format set to "No Format Set"?

 

[Marcelo Branco]

This worked for me

Sub atest()
    Dim rng As Range, vValue As Variant
    
    vValue = Range("B1").Value[COLOR=#ff0000]2[/COLOR]
    Set rng = Columns(1).Find(vValue, LookAt:=[COLOR=#ff0000]xlWhole[/COLOR], LookIn:=[COLOR=#ff0000]xlFormulas[/COLOR], MatchCase:=False)
    If Not rng Is Nothing Then MsgBox rng.Row
End Sub

M.

 

[Marcelo Branco]

I can't reproduce that using ctrl+F in Excel 2010. Does your Find & Replace dialog box have the format set to "No Format Set"?

Joe

Try Ctrl+F
Search Yes and in options: xlWhole
and you see what happens

M.

 

[footoo]

In the Find options set the number format to Accounting.

 

[JoeMo]

Joe

Try Ctrl+F
Search Yes and in options: xlWhole
and you see what happens

M.

Marcelo, works ok for me if I check "Entire cell contents" and search for "yes" in cells with an Accounting format, but stumbles on " @" or " @ " if the search is for "@" as I would expect it to do since the " @" and " @ " require "Entire cell contents" to be unchecked.

 

[Marcelo Branco]

Marcelo, works ok for me if I check "Entire cell contents" and search for "yes" in cells with an Accounting format

It worked for me only with
Entire cell contents
LookIn:Formulas

Didn't work at all with
Entire cell contents
LookIn:Values

M

 

[footoo]

Further to my post #6 :

Sub atest()
    Dim rng As Range, vValue As Variant
    
    vValue = Range("B1").Value2
    Application.FindFormat.NumberFormat = _
        "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    Set rng = Columns(1).Find(vValue, LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=False)
    If Not rng Is Nothing Then MsgBox rng.Row
End Sub

 

[MrKowz]

Thanks all - good to know the workarounds for this in case I run into the problem in the future. Still so strange that the value is not recognized simply because the number format of it has changed...

Cheers!