使用ActiveCell值与该单元格公式计算值对比的VBA问题

需要获取活动单元格（例如F3）的当前值，在该单元格运行VLOOKUP函数，判断函数计算结果是否与单元格当前值匹配。若不匹配，则用VLOOKUP结果替换当前值，并添加批注记录原数值；若匹配则直接跳转到下一个单元格，重复操作直到最后一列。

表格示例 #

用户原代码 #

Sub Grade_comment_Updated2()

Application.ScreenUpdating = False

lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column 
'填充至最后一列。最后一列由第2行包含数据的最右侧列定义'

ActiveSheet.Cells(3, 6).Select 
Do Until ActiveCell.Column = lastcolumn + 1  
    If ActiveCell.Value <> ActiveCell.FormulaR1C1 = 
    "=VLOOKUP(R[-3]C,C1:C4,4,0)".Value Then
        ActiveCell.ClearNotes
            ActiveCell.AddCommentThreaded ("Was " & ActiveCell.Value)
                Activecell.value = ActiveCell.FormulaR1C1 = 
                "=VLOOKUP(R[-3]C,C1:C4,4,0)".Value
                    ActiveCell.Offset(0, 1).Select
    ElseIf ActiveCell.Value = ActiveCell.FormulaR1C1 = 
    "=IFNA(VLOOKUP(R[-3]C,C1:C4,4,0),R[-1]C)".Value Then
        ActiveCell.Offset(0, 1).Select
End If
Loop
'查看单元格F3（当前已分配一个字母），然后在该单元格（F3）中运行VLOOKUP，判断字母是否与VLOOKUP查询的字母匹配；若不同则替换为新字母，并添加批注说明原字母，然后向右移动到下一个单元格重复操作'

Application.ScreenUpdating = True

MsgBox "Grade Update Complete"
    
End Sub

代码问题分析与修正 #

原代码存在多处语法和逻辑错误：

• 公式赋值与取值语法错误：不能用ActiveCell.FormulaR1C1 = "公式".Value的写法，需通过内存计算或正确设置公式后取值。
• 变量名大小写不一致：Activecell应为ActiveCell。
• 逻辑分支冗余：无需额外判断IFNA场景，可直接整合到计算逻辑中。
• 依赖Select/Activate操作：易因选中状态变化导致错误，效率较低。

修正后的代码：

Sub Grade_comment_Updated2()
    Application.ScreenUpdating = False
    
    Dim lastColumn As Long
    Dim currentCell As Range
    Dim vlookupResult As Variant
    
    '获取第2行最后一个有数据的列号
    lastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
    
    '从F3开始遍历到最后一列
    Set currentCell = ActiveSheet.Cells(3, 6)
    
    Do Until currentCell.Column > lastColumn
        '计算VLOOKUP结果，找不到则取上方单元格的值
        vlookupResult = Application.Evaluate("IFNA(VLOOKUP(R[-3]C,C1:C4,4,0),R[-1]C)")
        
        '判断当前值与计算结果是否不同
        If currentCell.Value <> vlookupResult Then
            currentCell.ClearNotes
            '添加批注记录原数值
            currentCell.AddCommentThreaded "Was " & currentCell.Value
            '替换为计算结果
            currentCell.Value = vlookupResult
        End If
        
        '移动到下一列
        Set currentCell = currentCell.Offset(0, 1)
    Loop
    
    Application.ScreenUpdating = True
    MsgBox "Grade Update Complete"
End Sub

修正说明 #

1. 取消Select/Activate操作：通过Range对象直接操作单元格，避免选中状态变化引发的错误，提升代码稳定性。
2. 内存计算公式结果：用Application.Evaluate直接在内存中计算VLOOKUP结果，无需修改单元格公式，完成判断后直接赋值。
3. 整合IFNA逻辑：将IFNA直接嵌入计算表达式，统一处理匹配失败的场景，简化分支判断。
4. 规范变量声明：显式声明变量，提升代码可读性与维护性。

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