adjusting function using mixed character instead of numeric when search item

[MKLAQ]

hello
I 've found this thread VBA- Using search message box
and update by Dave .

Sub CommandButton2_Click()
Dim Search          As Variant
Dim c               As Range
Dim sh              As Worksheet
Dim Response        As VbMsgBoxResult
    
Dim msg             As String, FirstAddress As String
Dim Prompts(1 To 2) As String, Prompt As String
    
Prompts(1) = "Serial number found On row(s) " & Chr(10) & Chr(10)
Prompts(2) = "Serial number Not found" & Chr(10) & Chr(10)
    
Set sh = ThisWorkbook.Worksheets("Sheet2")
    
Do
'display inputbox
Do
Search = InputBox("Enter Search Number Value:", "Search")
'cancel pressed
If StrPtr(Search) = 0 Then Exit Sub
Loop Until IsNumeric(Search)
        
Set c = sh.Columns(6).Find(What:=CLng(Search), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
msg = Prompts(1)
Do
msg = msg & c.Row & Chr(10)
Set c = sh.Columns(6).FindNext(c)
If c Is Nothing Then Exit Do
Loop Until FirstAddress = c.Address
Else
msg = Prompts(2) & Search & Chr(10)
End If
        
Response = MsgBox(msg & Chr(10) & "Do you want To make another search?", 36, "Results")
msg = ""
Loop Until Response = vbNo
    
End Sub

and see this is useful for me , but when search the item in column B should be the whole item is number , but in my case there are many items contains letters and numbers and symbols the code doesn't deal with non numeric items . how can I mod by using function deal with mixed character,please?

 

[dmt32]

Hi,
try making changes shown

Do
Search = InputBox("Enter Search Value:", "Search")
'cancel pressed
If StrPtr(Search) = 0 Then Exit Sub
Loop Until Len(Search) > 0

If IsNumeric(Search) Then Search = Val(Search)
     
Set c = sh.Columns(6).Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)

Dave

 

[MKLAQ]

thanks for fast answering !
much appreciated for new updating

 

[dmt32]

most welcome & appreciate feedback

Dave