View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Utkarsh Utkarsh is offline
external usenet poster
 
Posts: 18
Default Compating all rows in two sheets

I have two sheets with similar data

Sheet1
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 25
a 01-03-2008 x 124

Sheet2
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 20
a 01-03-2008 x 124
d 01-05-2007 x 100

I am using the below code to flag enties that are not exactly
matching. Thus it should flag for me that Name"b" and "d" are not
found in Sheet1. It works propoerly for "d" but not for "b". Obviously
I am amking a mistake. Please advise.

Sub compare()
'clear columne
Sheets("Sheet2").Range("E:E").ClearContents

'Find the last row to be evaluated for each sheet
Sheets("Sheet1").Select
lastrow1 = Range("A65536").End(xlUp).Row
Sheets("Sheet2").Select
lastrow2 = Range("A65536").End(xlUp).Row

Sheets("Sheet1").Select
For i = 2 To lastrow1
'Transfer each foled for a row into a variable
rec1 = Cells(i, 1).Value
rec2 = Cells(i, 2).Value
rec3 = Cells(i, 3).Value
rec4 = Cells(i, 4).Value


Sheets("Sheet2").Select
For j = 2 To lastrow2

'Look for a match
If Cells(i, 1).Value = rec1 And Cells(i, 2).Value = rec2 And _
Cells(i, 3).Value = rec3 And _
Cells(i, 4).Value = rec4 Then

Cells(i, 5).Value = "Found"

End If

Next j
Next i

End Sub