two lists of numbers
 

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?

two lists of numbers
 

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?

two lists of numbers
 

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?

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?

two lists of numbers
 

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?

two lists of numbers
 

Yes, you should increment the copy destination.


Dim Rng As Range
Dim Arr As Variant
Dim V As Variant
Dim Ndx As Integer
Dim Found As Boolean
Dim DestRng As Range
Set DestRng = Worksheets("Sheet2").Range("A1") '<<< CHANGE RANGE
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:=DestRng
Set DestRng = DestRng(2, 1)
Exit For
End If
Next Ndx
If Found = True Then
Exit For
End If
Next Rng

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

two lists of numbers
 

Thank you for your time and responses.

"Chip Pearson" wrote in message
...
Yes, you should increment the copy destination.


Dim Rng As Range
Dim Arr As Variant
Dim V As Variant
Dim Ndx As Integer
Dim Found As Boolean
Dim DestRng As Range
Set DestRng = Worksheets("Sheet2").Range("A1") '<<< CHANGE RANGE
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:=DestRng
Set DestRng = DestRng(2, 1)
Exit For
End If
Next Ndx
If Found = True Then
Exit For
End If
Next Rng

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