I have a worksheet with full of simple data and complex formulas. I want to find special cells which contain specific characters. Try to use the Find function, but not gives back the correct result because the cells Numberformat sometimes contains characters as of the cells values which i am looking for. This is a sample code to demonstrate the issue.
The target is to find all the cells which formulas contains percentage symbol (%).
For the demo place in cell F7 a formula e.g. =CONCATENATE(F5,"%","value"), and in cell F10 a string "only % values".
Tried xlValues but this attribute takes the formulas result (F7) and the cell formatting result (F5) as value also.

Sub demo()

Range("F5") = "sample"
Range("F5").NumberFormat = "@\%"
On Error Resume Next
i = Range("F:F").Find("%", , xlValues, xlPart).Row
On Error GoTo 0
If Err <> 0 Then MsgBox "No item found!": Exit Sub
k = i

Do While i <> j
j = Range("F:F").FindNext(Cells(k, "F")).Row
k = j
Debug.Print j  'here is the process
Loop

End Sub

This code results
7
10
5

If i replace in Find xlValues to xlFormulas the code results
10
7

I think this is because Find handle a user typed string in a cell as formula.

But the expected value is
7

It is not resolve the issue if extend the search for = sign also, because sometimes it is the target of the search. Till the time i am not looking for a complex udf search method in a custom code, i would know if there is a setting of the find method which results the required row numbers, or it is not a feasible way.

>Solution :

The target is to find all the cells which formulas contains percentage symbol (%).

One option is to use SpecialCells(xlCellTypeFormulas) and InStr:

Sub demo()
    Dim r As Range, cell As Range
    
    On Error Resume Next
    Set r = Range("F:F").SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    
    If r Is Nothing Then Exit Sub
    
    For Each cell In r
        If InStr(cell.Formula, "%") > 0 Then
            Debug.Print cell.Row
        End If
    Next
End Sub