At the end of running my macro I would really like it if a message box appeared displaying the results of the macro.
The message box would say "X" number discrepancies were identified, or "0" discrepancies were identified.
I am having great difficulty making this happen, i would really appreciate the help.
Please see my code below..
- This code compares the data in Sheet1 and Sheet 2 looking for any discrepancies.
- The results from this macro are then returned in Sheet 3.
- I have attached 3 images. Sheet1 and Sheet2 are the data being compared and sheet 3 is the result of the comparisons - showing any discrepancy.
The message box would say "X" number discrepancies were identified, or "0" discrepancies were identified.
I am having great difficulty making this happen, i would really appreciate the help.
Please see my code below..
- This code compares the data in Sheet1 and Sheet 2 looking for any discrepancies.
- The results from this macro are then returned in Sheet 3.
- I have attached 3 images. Sheet1 and Sheet2 are the data being compared and sheet 3 is the result of the comparisons - showing any discrepancy.
VBA Code:
Sub LookForDiscrepancies()
Dim varS1, varS2, varH1, varH2
Dim rngS1 As Range, rngS2 As Range
Dim c As Range, c1 As Range, c2 As Range
Dim iRow As Integer, iCol As Integer, i As Integer, iTest As Integer
Sheet1.Activate
Set rngS1 = Intersect(Sheet1.UsedRange, Columns("A"))
Sheet2.Activate
Set rngS2 = Intersect(Sheet2.UsedRange, Columns("A"))
Sheet3.Activate
Sheet3.Cells.Select
Ans = MsgBox(prompt:="Do you really want to delete data in Sheet3?", _
Buttons:=vbYesNo + vbExclamation, _
Title:="Delete Data!")
If Ans = vbNo Then Exit Sub ' vbNo = 7, vbYes = 6
Selection.Delete Shift:=xlUp
Sheet3.Rows("1:1").Value = Sheet1.Rows("1:1").Value
Let iRow = iRow + 2
With rngS2
'Search for Sheet1 IDs on Sheet2
For Each c1 In rngS1
On Error GoTo 0
Set c = .Find(what:=c1.Value) 'Look for match
If c Is Nothing Then 'Copy the ID to Sheet3
Sheet3.Cells(iRow, 1) = c1
Sheet3.Cells(iRow, 2) = "exist in sheet 1 not in sheet 2"
Let iRow = iRow + 1
Else 'Check if rows are identical
Let varS1 = Intersect(Sheet1.UsedRange, c1.EntireRow)
Let varS2 = Intersect(Sheet2.UsedRange, c.EntireRow)
Let iCol = Intersect(Sheet1.UsedRange, c1.EntireRow).Count
ReDim varH1(1 To iCol) As Integer
For i = 1 To iCol
If Not varS1(1, i) = varS2(1, i) Then
Let iTest = iTest + 1
Let varH1(i) = 1
End If
Next i
If iTest Then 'Rows are not identical
For i = 1 To iCol
Sheet3.Cells(iRow, i) = varS1(1, i)
If Not varH1(i) = 0 Then Cells(iRow, i) _
.Interior.ColorIndex = 40
Next i
Let iTest = 0
Let iRow = iRow + 1
End If
End If
Next
End With
Let iRow = iRow + 0
Range("A1").Offset(iRow, 0).Value = "Sheet2 vs Sheet1"
Let iRow = iRow + 2
With rngS1
'Search for Sheet2 IDs on Sheet1
For Each c2 In rngS2
On Error GoTo 0
Set c = .Find(what:=c2.Value) 'Look for match
If c Is Nothing Then 'Copy the ID to Sheet3
Sheet3.Cells(iRow, 1) = c2
Sheet3.Cells(iRow, 2) = "exist in sheet 2 not in sheet 1"
Let iRow = iRow + 1
Else 'Check if rows are identical
Let varS1 = Intersect(Sheet2.UsedRange, c2.EntireRow)
Let varS2 = Intersect(Sheet1.UsedRange, c.EntireRow)
Let iCol = Intersect(Sheet2.UsedRange, c2.EntireRow).Count
ReDim varH2(1 To iCol) As Integer
For i = 1 To iCol
If Not varS1(1, i) = varS2(1, i) Then
Let iTest = iTest + 1
Let varH2(i) = 1
End If
Next i
If iTest Then 'Rows are not identical
For i = 1 To iCol
Sheet3.Cells(iRow, i) = varS1(1, i)
If Not varH2(i) = 0 Then Cells(iRow, i) _
.Interior.ColorIndex = 36
Next i
Let iTest = 0
Let iRow = iRow + 1
End If
End If
Next
End With
Sheet3.Select 'resize the columns
Range("A:Z").Columns.AutoFit
End SubAttachments
Last edited by a moderator:
