Excel VBA对比两个工作簿报错求助：iRow_M = s1.UsedRange.Rows.Count行出错

报错核心原因 #

iRow_M = s1.UsedRange.Rows.Count报错是因为s1是Workbook（工作簿）对象，不能直接调用UsedRange属性，必须指定具体的工作表，比如默认第一个工作表s1.Worksheets(1)，或者明确的表名如s1.Worksheets("Sheet1")。

其他潜在问题及修复 #

除了上述报错，原代码还有几个隐藏问题：

• s3定义为Workbook但未初始化，调用s3.Cells会触发对象未设置错误
• 行号列号用Double类型不合适，应该用Long（整数类型，避免行号过大时溢出）
• 未处理两个文件行数/列数不一致的情况，会导致越界报错
• 直接对比单元格值未考虑空值、公式结果差异等场景

修正后的完整代码 #

Option Explicit
Sub Compare_Two_Excel_Sheets()
    ' 定义变量，行号列号用Long更适配Excel行号范围
    Dim Flag As Long
    Dim iR As Long, iC As Long, oRw As Long
    Dim iRow_M As Long, iCol_M As Long
    Dim maxRows As Long, maxCols As Long
    Dim s1 As Workbook, s2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim s3 As Workbook, ws3 As Worksheet
    
    Flag = 0
    oRw = 1
    
    ' 打开目标工作簿并绑定具体工作表
    Set s1 = Workbooks.Open(Filename:="C:\new\File1_Path.xlsx")
    Set ws1 = s1.Worksheets(1) ' 可替换为实际表名，如"Data"
    Set s2 = Workbooks.Open(Filename:="C:\new\File2_Path.xlsx")
    Set ws2 = s2.Worksheets(1)
    
    ' 初始化差异记录工作簿
    Set s3 = Workbooks.Add
    Set ws3 = s3.Worksheets(1)
    ws3.Name = "DIFF"
    ws3.Cells(1, 1) = "File1值"
    ws3.Cells(1, 2) = "File2值"
    ws3.Cells(1, 3) = "差异位置"
    
    ' 获取两个工作表的最大行列数，避免遍历越界
    iRow_M = ws1.UsedRange.Rows.Count
    iCol_M = ws1.UsedRange.Columns.Count
    maxRows = IIf(iRow_M > ws2.UsedRange.Rows.Count, iRow_M, ws2.UsedRange.Rows.Count)
    maxCols = IIf(iCol_M > ws2.UsedRange.Columns.Count, iCol_M, ws2.UsedRange.Columns.Count)
    
    ' 遍历所有单元格对比
    For iR = 1 To maxRows
        For iC = 1 To maxCols
            ' 清除原有高亮
            ws1.Cells(iR, iC).Interior.Color = xlNone
            ws2.Cells(iR, iC).Interior.Color = xlNone
            
            ' 对比单元格值，兼容空值场景
            If ws1.Cells(iR, iC).Value <> ws2.Cells(iR, iC).Value Then
                ' 高亮差异单元格
                ws1.Cells(iR, iC).Interior.Color = vbYellow
                ws2.Cells(iR, iC).Interior.Color = vbYellow
                
                ' 记录差异详情
                oRw = oRw + 1
                ws3.Cells(oRw, 1) = ws1.Cells(iR, iC).Value
                ws3.Cells(oRw, 2) = ws2.Cells(iR, iC).Value
                ws3.Cells(oRw, 3) = "行" & iR & "·列" & iC
                
                Flag = Flag + 1
            End If
        Next iC
    Next iR
    
    ' 反馈对比结果
    If Flag > 0 Then
        MsgBox "发现" & Flag & "处差异，已记录到DIFF工作表！"
    Else
        MsgBox "未发现差异！"
    End If

End Sub

关键修改说明 #

1. 把Workbook对象操作改为Worksheet对象，解决UsedRange调用报错
2. 变量类型替换为Long，避免大行数场景下的溢出问题
3. 取两个工作表的最大行列数遍历，防止因文件结构不一致导致越界
4. 新增差异记录工作簿，同时记录差异值和位置，便于快速定位
5. 优化提示信息，明确告知差异数量

内容的提问来源于stack exchange，提问作者Leo