Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find values from one worksheet in other worksheets
I have a list of numbers in "Sheet 1" that I need to find in one or more
different worksheets (ie: "Sheet 2" & "Sheet 3"). The number may or may not be found in the other worksheets. There also could be multiple rows that contain the number being found. How can I find these numbers and move the data from the rows the numbers are found in into a new worksheet (ie: "New Sheet"? Also, if there is no matching number found, is it possible to insert a row on the "New Sheet" saying "No data found"? Thank for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find values from one worksheet in other worksheets
See the help code example on the findnext method for sample code.
You question is too lacking in detail to provide anything else unless I made up all the parameters. -- Regards, Tom Ogilvy "Modell" wrote: I have a list of numbers in "Sheet 1" that I need to find in one or more different worksheets (ie: "Sheet 2" & "Sheet 3"). The number may or may not be found in the other worksheets. There also could be multiple rows that contain the number being found. How can I find these numbers and move the data from the rows the numbers are found in into a new worksheet (ie: "New Sheet"? Also, if there is no matching number found, is it possible to insert a row on the "New Sheet" saying "No data found"? Thank for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find values from one worksheet in other worksheets
Tom,
Here's what I have so far. Sub findnext() strFindString = Worksheets("Temp").Range("B2").Value With Worksheets("Countrywide Conditions").Range("b1:iv65000") Set c = .Find(strFindString, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Countrywide Conditions").Select c.EntireRow.Select Selection.Cut Sheets("Loans and Conditions").Select Range("B1").Select Selection.End(xlDown).Select ActiveCell.Offset(RowOffset + 1, ColumnOffset - 1).Activate ActiveSheet.Paste Sheets("Countrywide Conditions").Select Range("A1").Select Set c = .findnext(c) Loop While Not c Is Nothing And c.Address < firstAddress I'm running into a couple issues - 1. When the value in B2 can't be found anymore, the loop gets hung up and I get an error. How can I code it to see that the value in cell B2 can't be found anymore and to move on to looking for the value in cell B3? Thank you very much for your help. "Tom Ogilvy" wrote: See the help code example on the findnext method for sample code. You question is too lacking in detail to provide anything else unless I made up all the parameters. -- Regards, Tom Ogilvy "Modell" wrote: I have a list of numbers in "Sheet 1" that I need to find in one or more different worksheets (ie: "Sheet 2" & "Sheet 3"). The number may or may not be found in the other worksheets. There also could be multiple rows that contain the number being found. How can I find these numbers and move the data from the rows the numbers are found in into a new worksheet (ie: "New Sheet"? Also, if there is no matching number found, is it possible to insert a row on the "New Sheet" saying "No data found"? Thank for the help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find values from one worksheet in other worksheets
Maybe something like this (untested)
Sub findnext() Dim r as Range, cell as Range Dim c as Range Dim strFindString as String for each cell in worksheets("Temp").Range("B2:B3") strFindString = cell.value do set r = Worksheets("Countrywide Conditions" _ ).Range("b1:iv65000") Set c = r.Find(strFindString, LookIn:=xlValues) if not c is nothing then c.EntireRow.copy sheets("Loans and Conditions" _ ).Cells(rows.count,2).End(xlup).offset(1,-1) c.EntireRow.delete Else exit do End if loop Next End Sub Since it destroys data, test it on a copy of your workbook. Change B2:B3 on Temp to the range that contains values to search for (if more than 2 values) -- Regards, Tom Ogilvy "Modell" wrote: Tom, Here's what I have so far. Sub findnext() strFindString = Worksheets("Temp").Range("B2").Value With Worksheets("Countrywide Conditions").Range("b1:iv65000") Set c = .Find(strFindString, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Countrywide Conditions").Select c.EntireRow.Select Selection.Cut Sheets("Loans and Conditions").Select Range("B1").Select Selection.End(xlDown).Select ActiveCell.Offset(RowOffset + 1, ColumnOffset - 1).Activate ActiveSheet.Paste Sheets("Countrywide Conditions").Select Range("A1").Select Set c = .findnext(c) Loop While Not c Is Nothing And c.Address < firstAddress I'm running into a couple issues - 1. When the value in B2 can't be found anymore, the loop gets hung up and I get an error. How can I code it to see that the value in cell B2 can't be found anymore and to move on to looking for the value in cell B3? Thank you very much for your help. "Tom Ogilvy" wrote: See the help code example on the findnext method for sample code. You question is too lacking in detail to provide anything else unless I made up all the parameters. -- Regards, Tom Ogilvy "Modell" wrote: I have a list of numbers in "Sheet 1" that I need to find in one or more different worksheets (ie: "Sheet 2" & "Sheet 3"). The number may or may not be found in the other worksheets. There also could be multiple rows that contain the number being found. How can I find these numbers and move the data from the rows the numbers are found in into a new worksheet (ie: "New Sheet"? Also, if there is no matching number found, is it possible to insert a row on the "New Sheet" saying "No data found"? Thank for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing worksheet values in two worksheets | Excel Worksheet Functions | |||
Find repeated values in multiple worksheets and create summary worksheet | Excel Programming | |||
find and add values in different worksheets | Excel Programming | |||
How do I find and replace null values in Excel 2002 worksheets? | Excel Worksheet Functions | |||
Find ALL WorkSheets according to 2 ComboBox Values.... Help with Code | Excel Programming |