| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming,microsoft.public.vb.general
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Making Lists of numbers | Excel Discussion (Misc queries) | |||
| How to determine the correlation between two lists of numbers? | Excel Discussion (Misc queries) | |||
| Converting lists of numbers | Excel Discussion (Misc queries) | |||
| solving lists of numbers problems | Excel Discussion (Misc queries) | |||
| How do I compare two lists of numbers? | Excel Worksheet Functions | |||
Linear Mode
