使用宏比较2个excel文件

Question

我只是想知道是否有任何方法来比较使用宏的两个优秀的电子表格。我有一个宏，基本上是做这个工作，但它检查一列一列。因此，假设我在A(1,1)中sheet1中定义了一个值，如果在sheet2中的A(1,1)中不存在相同的值，但是列的任何行中都存在相同的值，那么它不会引起抱怨。

'compare Sheet
Sub CompareTable()
Dim tem, tem1 As String
Dim text1, text2 As String
Dim i As Integer, hang1 As Long, hang2 As Long, lie As Long, maxhang As Long, maxlie As Long
    Sheets("Sheet1").Select
    Columns("A:A").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1").Select

    Sheets("Sheet2").Select
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    Rows("1:" & lastRow).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    maxhang = lastRow       ' number of the last row containg data
    MaxRow = lastRow
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MaxColumn = LastCol
    For col = 1 To MaxColumn
        For hang1 = 2 To maxhang
            Dim a As Integer
            a = 0
            tem = Sheets(1).Cells(hang1, col)
            For hang2 = 1 To maxhang
                tem1 = Sheets(2).Cells(hang2, col)
                If tem1 = tem Then
                a = 1
                Sheets(2).Cells(hang2, col).Interior.ColorIndex = 6
                For lie = 1 To maxlie
                    text1 = Sheets(1).Cells(hang1, lie)
                    text2 = Sheets(2).Cells(hang2, lie)
                    If text1 <> text2 Then
                    Sheets(2).Cells(hang2, lie).Interior.ColorIndex = 8
                    End If
                Next
                End If
            Next

            If a = 0 Then
            Sheets(1).Cells(hang1, 1).Interior.ColorIndex = 5
            End If
        Next
    Next
End Sub

注意:我正在寻找任何可能给我提供行匹配的解决方案，所以如果给定行的任何值与sheet2不匹配，那么它应该突出显示它。

我也愿意有其他的选择。如有任何帮助或建议，将不胜感激。

谢谢你抽出时间！

Answer 1

我不确定这是不是你所期望的。请看我下面的代码

Sub CompareTable()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Dim table1 As Range
    Dim table2 As Range

    Dim table1Rows As Integer
    Dim table1Cols As Integer

    Set ws1 = Worksheets("sheet1")
    Set ws2 = Worksheets("sheet2")
    Set table1 = ws1.Cells
    Set table2 = ws2.Cells

    table1Rows = ws1.UsedRange.Rows.Count
    table1Cols = ws1.UsedRange.Columns.Count

    For i = 1 To table1Rows
        For j = 1 To table1Cols
            If table1(i, j).Value = table2(i, j).Value Then
            Else
            ws1.Cells(i, j).Interior.Color = vbYellow
            End If

        Next

    Next

End Sub

Sheet1表

Sheet2表

​

运行代码tgisis后，我的结果

​

​