VBA实现比较

最新推荐文章于 2024-07-27 17:56:15 发布

小何小何h

最新推荐文章于 2024-07-27 17:56:15 发布

阅读量961

点赞数 1

文章标签： java

本文链接：https://blog.csdn.net/weixin_53754024/article/details/130232427

版权

Sub check_Click()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r1 As Range, r2 As Range
    Dim i As Long, j As Long
    Dim diffCount As Long
    Dim maxRows As Long, maxCols As Long

    ' 设置要比较的两个工作表
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    ' 设置要比较的范围
    Set r1 = ws1.UsedRange
    Set r2 = ws2.UsedRange

    ' 初始化差异计数器
    diffCount = 0

    ' 计算最大行数和列数
    maxRows = Application.WorksheetFunction.Max(r1.Rows.Count, r2.Rows.Count)
    maxCols = Application.WorksheetFunction.Max(r1.Columns.Count, r2.Columns.Count)

    ' 为两个工作表添加新列以记录差异计数
    ws1.Cells(1, maxCols + 1).Value = "Difference Count"
    ws2.Cells(1, maxCols + 1).Value = "Difference Count"

    ' 遍历两个工作表中的单元格
    For i = 1 To maxRows
        For j = 1 To maxCols
        
        ' 比较单元格内容之前，将数值单元格转换为字符串
            Dim cellValue1 As String, cellValue2 As String
            cellValue1 = CStr(r1.Cells(i, j).Value)
            cellValue2 = CStr(r2.Cells(i, j).Value)

            ' 如果单元格内容不同，用颜色标记并更新差异计数
            If r1.Cells(i, j).Value <> r2.Cells(i, j).Value Then
                r1.Cells(i, j).Interior.Color = RGB(255, 192, 203)
                r2.Cells(i, j).Interior.Color = RGB(0, 255, 127)
                diffCount = diffCount + 1

                ' 更新该行的差异计数
                ws1.Cells(i, maxCols + 1).Value = ws1.Cells(i, maxCols + 1).Value + 1
                ws2.Cells(i, maxCols + 1).Value = ws2.Cells(i, maxCols + 1).Value + 1
            End If
        Next j
    Next i

    ' 显示差异总数
    MsgBox diffCount & " differences found.", vbInformation

End Sub