Hello All

Some time ago I worked on a macro to compare data on one worksheet with that
in two other worksheets.

This macro was designed to check whether any records located in the
€œFiltered Data€� worksheet already exist in either the €œOutstanding€� or
€œComplete€� worksheets. This was done by comparing a unique identifier (i.e.
the €œAGS Number€� in Column A).

If the record doesnt already exist, then a copy of the record is placed in
the €œAdditions€� worksheet.

If the record already exists in the €œOutstanding€� worksheet, then a
comparison of the €œend date€� field is made. If the end date is different, a
copy of the record is placed in the €œChanges€� worksheet. If it isnt
different, then a copy is placed in the €œIgnored€� worksheet.

Likewise, if the record already exists in the €œComplete€� worksheet, then a
copy of it is placed in the €œIgnored€� worksheet.

The same record cannot appear in both the €œOutstanding€� and €œComplete€�
worksheets.

In theory, then, by adding the total number of records located in the
€œIgnored€�, €œAdditions€� and €œChanges€� worksheets, we should get the same
number of records located in the €œFiltered Data€� worksheet.

This isnt happening and thats why I need your help! The following code was
written about six months ago with the help of other people and now Im at a
bit of a loss to find where the problem is:



Sub CompareData()

Sheets("Ignored").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Additions").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Changes").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Sh1RowCount = 1
Sh3RowCount = 1
Sh4RowCount = 1
Sh5RowCount = 1
With Sheets("Filtered Data")

Do While .Range("A" & Sh1RowCount) < ""

SearchItem = .Range("A" & Sh1RowCount)

With Sheets("Complete")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("Outstanding")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Additions").Rows(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
Else
'compare end dates

If IsDate(.Range("K" & Sh1RowCount)) = True And
IsDate(c.Offset(0, 10)) = True Then
If CDate(.Range("K" & Sh1RowCount)) _
CDate(c.Offset(0, 10)) Then

.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Changes").Rows(Sh4RowCount)
Sh4RowCount = Sh4RowCount + 1

End If
End If

End If

Else
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Ignored").Rows(Sh5RowCount)
Sh5RowCount = Sh5RowCount + 1

End If

Sh1RowCount = Sh1RowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub



If it helps, below are the headings associated with each column:

A AGS Number
B First Name
C Last Name
D User ID
E BSL
F Location
G Acting Grade
H Base Grade
I Reason
J Start Date
K End Date


I hope this makes sense€¦

Thanks for your help!

Joe.

--
If you can measure it, you can improve it!