Search more than number

[gnusmas]

hi friend i have this vba code just to serach 1 number my hope how to change this code to search more than number!!

Sub searchnumber()
Dim lc As Long, Num As Variant, r As Range, Col As Range, ct As Long
Num = InputBox("Enter the number to search for")
If Num = "" Then
Exit Sub
ElseIf IsNumeric(Num) Then
Num = Val(Num)
Else
MsgBox "You must enter a number"
Exit Sub
End If
Set r = Sheets("Sheet3").Range("A1").CurrentRegion
For Each Col In r.Columns
If WorksheetFunction.CountIf(Col, Num) > 0 Then
ct = ct + 1
If ct > 1 Then
Col.Copy Destination:=Sheets("Sheet5").Cells(1, Sheets("Sheet5").Cells(1, Columns.Count).End(xlToLeft).Column + 1)
Else
Col.Copy Destination:=Sheets("Sheet5").Cells(1, 1)
End If
End If
Next Col
End Sub

THANK YOU FOR HELP!!

 

[ghrain22]

The variable is a variant. You just need to remove the conditional that checks if the variable is numeric.

[COLOR=#333333]Sub searchnumber()[/COLOR]
[COLOR=#333333]Dim lc As Long, Num As Variant, r As Range, Col As Range, ct As Long[/COLOR]
[COLOR=#333333]Num = InputBox("Enter the number to search for")[/COLOR]
[COLOR=#333333]If Num = "" Then[/COLOR]
[COLOR=#333333]Exit Sub[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Set r = Sheets("Sheet3").Range("A1").CurrentRegion[/COLOR]
[COLOR=#333333]For Each Col In r.Columns[/COLOR]
[COLOR=#333333]If WorksheetFunction.CountIf(Col, Num) > 0 Then[/COLOR]
[COLOR=#333333]ct = ct + 1[/COLOR]
[COLOR=#333333]If ct > 1 Then[/COLOR]
[COLOR=#333333]Col.Copy Destination:=Sheets("Sheet5").Cells(1, Sheets("Sheet5").Cells(1, Columns.Count).End(xlToLeft).Column + 1)[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Col.Copy Destination:=Sheets("Sheet5").Cells(1, 1)[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next Col[/COLOR]
[COLOR=#333333]End Sub[/COLOR]