View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
Chip Pearson Chip Pearson is offline
external usenet poster
 
Posts: 7,247
Default two lists of numbers

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?