DLopez79 said:
I have two spreadsheets that contain almost the same data. I want to compare
the spreadsheets and determine what is the difference between the
spreadsheets.
The following macro will show the differences between sheet1 and sheet2.
Cells that are different will be listed to the side of sheet 2 table. i.e.
if the contents of b2 on sheet 1 differs from b2 on sheet 2 then b2 is listed
on sheet 2 and you can make and inspection manually.
Copy the macro into a Viual basic module (Alt + F11, Insert, Module) and
press f5 to run the procedure.
Dim rng1 As Range, rng2 As Range
Dim i As Long, i2 As Long, j As Integer, j2 As Integer
Dim nr As Long, nr2 As Long, nc As Integer, nc2 As Integer
Sub compare()
Dim msg As String, count As Long, summary
' 'Select sheet 2
Sheets("Sheet2").Select
' set the ranges to compare
Set rng2 = Range("A1").CurrentRegion
Set rng1 = Sheets("Sheet1").Range("A1").CurrentRegion
nr2 = rng2.Rows.count
nc2 = rng2.Columns.count
nr = rng1.Rows.count
nc = rng1.Columns.count
count = 0
' chack thet the number of
If nr <> nr2 Then
MsgBox "The number of rows is different"
Exit Sub
ElseIf nc <> nc2 Then
MsgBox "The number of Columns is different"
Exit Sub
End If
For i = 1 To nr
msg = ""
For j = 1 To nc
If Cells(i, j) <> rng1.Cells(i, j) Then
'Display cells that do not agree
msg = msg & " " & Cells(i, j).Address
Cells(i, nc2 + 2) = msg
count = count + 1
End If
Next
Next
summary = MsgBox("There were " & count & " errors in the tables!", , "N
Differences in Sheet1 & Sheet2")
End Sub
Regards
Peter
