VBA to search Excel by Field Input Values

[SanjayGMusafir]

Hi Experts
I have fiel where I put 10 to 12 digit numbers. The Column is formatted as "##### #####" to give a space to make number readable like in India.

If I input "1234567890" it shows me "12345 67890"

Now I'm using a VBA that searches and return from xLValues. It works perfect if I search for an thing in output format. for eg. "12345 67890"

Just now got in a situation where I need to search by "1234567890" and it should take me to the cell where "12345 67890" is displayed.

Any help is highly appreciated.

Thanks a lot

This is the complete code I'm using right now -

Sub SearchSheet()
'
' SearchSheet Macro
'

'
    Dim xRng As Range
    
    x = Application.InputBox("", "Search for...")
    
    'To reset Zoom levels of the sheet
    Call ZoomReset
    
    Application.ScreenUpdating = True
    Range("A1").Select
    
    If x = "" Then 'if Search Input is Blank
    
        MsgBox "Blank Search..."
        Exit Sub
    
    ElseIf x = False Then 'If Search is Cancelled
        
        MsgBox "You Cancelled..."
        Exit Sub
            
    Else
        Set xRng = Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False)
        
        If xRng Is Nothing Then 'If No Result Found
    
            MsgBox "No Search Result Found..."
            Exit Sub
                
        Else
            
            xRng.Select 'When Result is Found
            
        End If
    End If
    
End Sub

I experimented changing a part of FIND from "xlValues" to "xlFormulas" but it didn't work. Probably, I'm missing some trick.

Set xRng = Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False)

 

[kevin9999]

Before your Set xRng = line, put this:

x = Format(x, "##### #####")

and see if that makes a difference.

 

[SanjayGMusafir]

Before your Set xRng = line, put this:

x = Format(x, "##### #####")

and see if that makes a difference.

Thanks @kevin9999 that did the magic.

Highly appreciated.

 

[kevin9999]

Happy to help Sanjay, and thanks for the feedback