Find values from one worksheet in other worksheets

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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)