If that's the case, then you probably have to loop through the
range. Find won't do it.
Dim Rng As Range
Dim Arr As Variant
Dim V As Variant
Dim Ndx As Integer
Dim Found As Boolean
V = InputBox("Enter Number")
For Each Rng In Range("A1:A10") '<<< CHANGE RANGE
Arr = Split(Rng.Text, ",")
For Ndx = LBound(Arr) To UBound(Arr)
If Arr(Ndx) = V Then
Found = True
Rng.EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
Exit For
End If
Next Ndx
If Found = True Then
Exit For
End If
Next Rng
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Chas" wrote in message
. ..
I have to be careful here. The cell may contain: 5,15,19,20 If
the user entered a 9 in the inputbox, I don't want a match.
"Chip Pearson" wrote in message
...
Try something like
Dim V As Variant
Dim FoundCell As Range
V = InputBox("Enter number")
Set FoundCell = Range("A:A").Find(what:=V)
If Not FoundCell Is Nothing Then
FoundCell.EntireRow.Copy
Destination:=Worksheets("sheet2").Range("A1")
End If
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Chas" wrote in message
...
I am writing an Excel VBA that prompts the user for a series
of numbers. Next I want to compare that list against each cell
in a column. If true, then copy that entire row to another
sheet.
I prefer not to have a nested For/Next loop. Is there
another way to do this?
For the VBA people, any simple examples?