In the .Copy, don't I have to increment .RANGE to copy to then next row?

Also, how do I know if I am at the last row? In your example, range a1-a10
may have an empty cell. But b1-b10 will always have something until the end
of rows is reached.

"Chip Pearson" wrote in message
...
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?