I'm having trouble with Find and FindNext (I think). I have 2 sheets with
rows of data containing ID1, ID2, and other data (in that column order). I
want to find the row on Sheet2 that contains the same ID1 and ID2 as in a
single row on Sheet1, and mark (in a column to the left) on Sheet1 if ID1 and
ID2 is found, or just ID1, or ID1 is not found on Sheet2.

The code below correctly marks rows as "Found" (i.e., both ID1 and ID2 are
found) and "NOT FOUND: ID1". But "NOT FOUND: ID2" (which should be when ID1
is only found without the proper ID2) is reported whenever there is more than
one row with ID1, even when one of the rows has the same ID2.

Worksheets("Sheet1").Range("ID1_on_Sheet1").Select
For Each Item In Selection
With Worksheets("Sheet2").Range(ID1_on_Sheet2)
Set ID1_Cell = .Find(Item.Value, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows)
If Not ID1_Cell Is Nothing Then
firstAddress = ID1_Cell.Address
Do
If Item.Offset(0, 1).Value = ID1_Cell.Offset(0, 1).Value
Then 'Compares ID2
Item.Offset(0, -1).Value = "Found"
Else: Item.Offset(0, -1).Value = "NOT FOUND: ID2"
End If
Set ID1_Cell = .FindNext(ID1_Cell)
Loop While ID1_Cell.Address < firstAddress
End If
If ID1_Cell Is Nothing Then Item.Offset(0, -1).Value = "NOT FOUND:
ID1"
End With
Next Item

Any help is much appreciated!

You have one line in the wrong place and an extra "Not Found". You only need
the "Not found" with the Find and not with the "find Next".

Worksheets("Sheet1").Range("ID1_on_Sheet1").Select
For Each Item In Selection
With Worksheets("Sheet2").Range(ID1_on_Sheet2)
Set ID1_Cell = .Find(Item.Value, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows)
If Not ID1_Cell Is Nothing Then
firstAddress = ID1_Cell.Address
Do
If Item.Offset(0, 1).Value = ID1_Cell.Offset(0, 1).Value
Then 'Compares ID2
Item.Offset(0, -1).Value = "Found"
End If
Set ID1_Cell = .FindNext(ID1_Cell)
Loop While ID1_Cell.Address < firstAddress
else
Item.Offset(0, -1).Value = "NOT FOUND:
End If
ID1"
End With
Next Item


"DJ" wrote:

I'm having trouble with Find and FindNext (I think). I have 2 sheets with
rows of data containing ID1, ID2, and other data (in that column order). I
want to find the row on Sheet2 that contains the same ID1 and ID2 as in a
single row on Sheet1, and mark (in a column to the left) on Sheet1 if ID1 and
ID2 is found, or just ID1, or ID1 is not found on Sheet2.

The code below correctly marks rows as "Found" (i.e., both ID1 and ID2 are
found) and "NOT FOUND: ID1". But "NOT FOUND: ID2" (which should be when ID1
is only found without the proper ID2) is reported whenever there is more than
one row with ID1, even when one of the rows has the same ID2.

Worksheets("Sheet1").Range("ID1_on_Sheet1").Select
For Each Item In Selection
With Worksheets("Sheet2").Range(ID1_on_Sheet2)
Set ID1_Cell = .Find(Item.Value, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows)
If Not ID1_Cell Is Nothing Then
firstAddress = ID1_Cell.Address
Do
If Item.Offset(0, 1).Value = ID1_Cell.Offset(0, 1).Value
Then 'Compares ID2
Item.Offset(0, -1).Value = "Found"
Else: Item.Offset(0, -1).Value = "NOT FOUND: ID2"
End If
Set ID1_Cell = .FindNext(ID1_Cell)
Loop While ID1_Cell.Address < firstAddress
End If
If ID1_Cell Is Nothing Then Item.Offset(0, -1).Value = "NOT FOUND:
ID1"
End With
Next Item

Any help is much appreciated!

Joel,
Thanks for your help!

DJ

"Joel" wrote:

You have one line in the wrong place and an extra "Not Found". You only need
the "Not found" with the Find and not with the "find Next".

Worksheets("Sheet1").Range("ID1_on_Sheet1").Select
For Each Item In Selection
With Worksheets("Sheet2").Range(ID1_on_Sheet2)
Set ID1_Cell = .Find(Item.Value, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows)
If Not ID1_Cell Is Nothing Then
firstAddress = ID1_Cell.Address
Do
If Item.Offset(0, 1).Value = ID1_Cell.Offset(0, 1).Value
Then 'Compares ID2
Item.Offset(0, -1).Value = "Found"
End If
Set ID1_Cell = .FindNext(ID1_Cell)
Loop While ID1_Cell.Address < firstAddress
else
Item.Offset(0, -1).Value = "NOT FOUND:
End If
ID1"
End With
Next Item


"DJ" wrote:

I'm having trouble with Find and FindNext (I think). I have 2 sheets with
rows of data containing ID1, ID2, and other data (in that column order). I
want to find the row on Sheet2 that contains the same ID1 and ID2 as in a
single row on Sheet1, and mark (in a column to the left) on Sheet1 if ID1 and
ID2 is found, or just ID1, or ID1 is not found on Sheet2.

The code below correctly marks rows as "Found" (i.e., both ID1 and ID2 are
found) and "NOT FOUND: ID1". But "NOT FOUND: ID2" (which should be when ID1
is only found without the proper ID2) is reported whenever there is more than
one row with ID1, even when one of the rows has the same ID2.

Worksheets("Sheet1").Range("ID1_on_Sheet1").Select
For Each Item In Selection
With Worksheets("Sheet2").Range(ID1_on_Sheet2)
Set ID1_Cell = .Find(Item.Value, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows)
If Not ID1_Cell Is Nothing Then
firstAddress = ID1_Cell.Address
Do
If Item.Offset(0, 1).Value = ID1_Cell.Offset(0, 1).Value
Then 'Compares ID2
Item.Offset(0, -1).Value = "Found"
Else: Item.Offset(0, -1).Value = "NOT FOUND: ID2"
End If
Set ID1_Cell = .FindNext(ID1_Cell)
Loop While ID1_Cell.Address < firstAddress
End If
If ID1_Cell Is Nothing Then Item.Offset(0, -1).Value = "NOT FOUND:
ID1"
End With
Next Item

Any help is much appreciated!