# Search more than number

#### gnusmas

##### Board Regular
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!!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### ghrain22

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

Code:
``````[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]``````

Replies
4
Views
940
Replies
5
Views
350
Replies
3
Views
761
Replies
3
Views
372
Replies
4
Views
700

1,190,913
Messages
5,983,532
Members
439,848
Latest member
timmyo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back