PDA

View Full Version : Comparison of 2 versions of Excel sheet



NM123
11-17-2011, 10:07 PM
Hi All,
I have attached two xl sheet which are of different versions in the zip folder. I want to compare both the sheet and track the changes in a new sheet. Below is the my code, for some reason it is not working.
Can you please have a look into it.




Sub test()
Dim a, i As Long, ii As Long, w(), temp, flg As Boolean
With Workbooks.Open(ThisWorkbook.Path & "\OldVersion.xls")
a = .Sheets(1).Range("a1").CurrentRegion.Value
.Close False
End With
ReDim w(1 To UBound(a, 2))
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
For ii = 1 To UBound(a, 2)
w(ii) = a(i, ii)
Next
.Item(a(i, 1)) = w
Next
a = ThisWorkbook.Sheets(1).Range("a1").CurrentRegion.Value
For i = 2 To UBound(a, 1)
If .exists(a(i, 1)) Then
w = .Item(a(i, 1))
For ii = 2 To UBound(a, 2)
If w(ii) <> a(i, ii) Then
temp = a(i, ii): a(i, ii) = ""
a(i, ii) = "Was : " & w(ii) & vbLf & _
"Now : " & temp
flg = True
End If
Next
If Not flg Then
a(i, 1) = ""
flg = False
End If
End If
Next
End With
With ThisWorkbook.Sheets(2)
With .Range("a1").Resize(UBound(a, 1), UBound(a, 2))
.Value = a
On Error Resume Next
.Columns(1).SpecialCells(4).EntireRow.Delete
On Error GoTo 0
End With
End With

End Sub



Hope this clears You.

Regards,
NM