These are the test and results from running the macro.

Test: book1 and book2 with identical products and prices.
Expected Result: should find no difference so nothing written to
book3.
Actual Result: every row from book1 was written to book3

Test: book1 has one additional product compared to book2
Expected Result: row containing additional product written to book3
Actual Result - run from book1: all rows from book1 written to book3
followed by all rows from book2 from after the corresponding product
in book1 (i.e. new product was in row 5 of book1 so all products from
row 5 down in book2 were written to book3)
Actual Result - run from book2: all rows from book1 written to book3
apart from last row, followed by all rows book2 from after the
corresponding product in book1

Test: book1 has one product with different price compared to book2
Expected Result: row containing product with different price from
book1 written to book3
Actual Result -run from book2: All rows from book1 written to book3
followed by corresponding row of changed price row from book2 written
to book3
Actual Result - run from book1: All rows from book1 are written to
book3 followed by all rows from book2.

When I mix up the order of products I get products all over the place.
I'm still trying to workout where they are coming from for that test.

Here is the complete code


Sub CheckPrices()

Sub CheckPrices()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet

Dim Ws1A As Range
Dim Ws2A As Range

Dim lWb1Row As Long
Dim lWb2Row As Long
Dim lws3VacRow As Long

Dim Cell As Range

Dim sProd As String

Set Ws1 = Workbooks("book1.xls").Sheets("Sheet1")
Set Ws2 = Workbooks("book2.xls").Sheets("Sheet1")
Set Ws3 = Workbooks("book3.xls").Sheets("Sheet1")

Set Ws1A = Ws1.Columns("A")
Set Ws2A = Ws2.Columns("A")

' compare book 1 against book 2
For Each Cell In Ws1.Range("A1:a" & Range("a65536").End(xlUp).Row)
lWb2Row = 0
If Not IsEmpty(Cell) Then
On Error Resume Next
lWb2Row = Ws2A.Find(What:=Cell.Value, After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row_
On Error GoTo 0
If lWb2Row 0 Then
If Ws1.Range("b" & Cell.Row) < Ws2.Range("b" & lWb2Row) Then
lws3VacRow = Ws3.Range("a65536").End(xlUp).Row + 1
Ws1.Rows(Cell.Row).Copy Destination:=Ws3.Rows(lws3VacRow)
End If
Else
lws3VacRow = Ws3.Range("a65536").End(xlUp).Row + 1
Ws1.Rows(Cell.Row).Copy Destination:=Ws3.Rows(lws3VacRow)
End If
End If
Next Cell


'compare book 2 against book 1
For Each Cell In Ws1.Range("A2:a" & Range("a65536").End(xlUp).Row)
lWb1Row = 0
If Not IsEmpty(Cell) Then
On Error Resume Next
lWb1Row = Ws2A.Find(What:=Cell.Value, After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row
On Error GoTo 0
If lWb1Row 0 Then
If Ws2.Range("b" & Cell.Row) < Ws1.Range("b" & lWb1Row) Then
lws3VacRow = Ws3.Range("a65536").End(xlUp).Row + 1
Ws2.Rows(Cell.Row).Copy Destination:=Ws3.Rows(lws3VacRow)
End If
Else
lws3VacRow = Ws3.Range("a65536").End(xlUp).Row + 1
Ws2.Rows(Cell.Row).Copy Destination:=Ws3.Rows(lws3VacRow)
End If
End If

Next Cell

End Sub




On Wed, 7 Jan 2004 18:25:34 -0600, mudraker
wrote:

DireWolf

Sorry I forgot you wanted the entire row my code only copied column A


this same instruction needs to be replaced in 2 locations

Ws3.Range("a" & lws3VacRow).Value = Cell.Value

to


1st change
ws1.rows(Cell.row).copy Destination:=Ws3.Rows(lws3VacRow)


2nd change
ws2.rows(Cell.row).copy Destination:=Ws3.Rows(lws3VacRow

Some Products will be listed twice as macro checks book1 products
against book 2 products and lists diffences in book3

it then does the same for checking book 2 against book 1

Just in case I am misunderstanding your problem please paste all of
your code


---
Message posted from http://www.ExcelForum.com/