VBA代码报错修复：对比两工作表单元格值并高亮差异

问题背景 #

• 两个工作表："Data Pulls"（含Bloomberg实时数据公式）、"Hardcoded Values"（存储对应静态数值/文本）
• 需求：编写按钮代码遍历A8:W46区域，对比两表对应单元格，将"Data Pulls"中值不同的单元格标红
• 当前代码触发Run-time error '13': Type Mismatch，转文本处理无效

错误原因 #

1. Bloomberg公式可能返回错误值（如#N/A、#VALUE!），CStr()无法转换错误值，直接触发类型不匹配
2. 未声明循环变量i、j，变体类型隐式转换易引发问题
3. 统一转文本对比未考虑数值精度差异，容易出现误判

修复后的代码 #

Sub HighlightDifferences()
    ' 声明变量
    Dim srcRange As Range
    Dim dstRange As Range
    Dim i As Integer, j As Integer
    Dim srcVal As Variant, dstVal As Variant
    
    ' 定义对比区域
    Set srcRange = ThisWorkbook.Sheets("Data Pulls").Range("A8:W46")
    Set dstRange = ThisWorkbook.Sheets("Hardcoded Values").Range("A8:W46")
    
    ' 清除之前的高亮标记
    srcRange.Interior.ColorIndex = xlColorIndexNone
    
    ' 遍历每个单元格
    For i = 1 To srcRange.Rows.Count
        For j = 1 To srcRange.Columns.Count
            srcVal = srcRange.Cells(i, j).Value2
            dstVal = dstRange.Cells(i, j).Value2
            
            ' 跳过双方均为错误值的情况（可根据需求调整）
            If IsError(srcVal) And IsError(dstVal) Then
                GoTo NextCell
            End If
            
            ' 单个单元格为错误值时直接标红
            If IsError(srcVal) Or IsError(dstVal) Then
                srcRange.Cells(i, j).Interior.ColorIndex = xlRed
                GoTo NextCell
            End If
            
            ' 按数据类型区分对比逻辑
            Select Case VarType(srcVal)
                ' 数值类型：考虑浮点精度误差
                Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
                    If Abs(srcVal - dstVal) > 0.000001 Then
                        srcRange.Cells(i, j).Interior.ColorIndex = xlRed
                    End If
                ' 文本类型：统一转大写避免大小写误判
                Case vbString
                    If UCase(srcVal) <> UCase(dstVal) Then
                        srcRange.Cells(i, j).Interior.ColorIndex = xlRed
                    End If
                ' 其他类型（如日期）直接对比
                Case Else
                    If srcVal <> dstVal Then
                        srcRange.Cells(i, j).Interior.ColorIndex = xlRed
                    End If
            End Select
            
NextCell:
        Next j
    Next i
End Sub

修复说明 #

• 显式声明循环变量i、j为整数，避免变体类型隐式转换问题
• 增加错误值判断逻辑，灵活处理Bloomberg公式返回的异常值
• 分类型对比：数值型考虑浮点精度误差，文本型统一转大写，避免无意义的误判
• 新增清除旧高亮的步骤，避免残留标记干扰判断

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