Excel VBA批量替换单元格值异常：所有目标单元格均被设为E3值

问题描述 #

我有一个包含多个工作表的Excel文件，其中大量数据行需手动修改，因此尝试通过VBA实现自动化。创建了名为“Group Code Correction”的工作表，用于列出需修改的行并输入新值。编写的VBA代码如下：

Private Sub CommandButton1_Click()

Dim r As Long
Dim g As Long
Dim lrow As Long
Dim grow As Long
Dim ysheet As String
Dim ws As Worksheet

ysheet = Sheets("Group Code Correction").Range("B3").Value 'define ysheet by cell B3
Set ws = Sheets(ysheet) 'Define ws by ysheet
grow = Sheets("Group Code Correction").Range("D" & Rows.Count).End(xlUp).row 'find last row in sheet group code correction
lrow = ws.Range("D" & Rows.Count).End(xlUp).row 'Find last row in sheet ws

For g = 3 To grow 'loop from row 3 to last row

    If Sheets("Group Code Correction").Range("E3") = "" Then 'if E3 is blank do nothing
        Else
            For r = 2 To lrow 'Loop from row 2 to last row
                If ws.Range("D" & r) = Sheets("Group Code Correction").Range("D" & g).Value And ws.Range("Z" & r) = Sheets("Group Code Correction").Range("C" & g).Value Then 'if Dr matches Dg and Zr matches Cg
                    ws.Range("Z" & r) = Sheets("Group Code Correction").Range("E" & g).Value 'replace Zr with Eg
                End If
            Next r

    End If

Next g

End Sub

代码能定位到正确的单元格，但所有被修改的单元格均被替换为E3的值，而非对应行的E3、E4、E5等值，请求指点修正方向。

问题根源 #

核心错误在于判断是否执行替换的条件中，固定引用了E3单元格：

If Sheets("Group Code Correction").Range("E3") = "" Then

这段代码会始终检查第3行E列的值是否为空，而不是当前循环到的第g行E列。这会导致两种异常情况：

• 若E3不为空：所有行的修改逻辑都会执行，哪怕当前g行的E列是空值
• 若E3为空：所有行的修改逻辑都不会执行，哪怕后续行的E列有有效值

修正后的代码 #

把判断条件中的E3改为E" & g，确保每次循环检查当前行的E列是否为空，同时优化代码可读性：

Private Sub CommandButton1_Click()

Dim r As Long
Dim g As Long
Dim lrow As Long
Dim grow As Long
Dim ysheet As String
Dim ws As Worksheet
Dim correctionWs As Worksheet

' 提前获取修正工作表对象，减少重复调用
Set correctionWs = Sheets("Group Code Correction")
ysheet = correctionWs.Range("B3").Value
Set ws = Sheets(ysheet)

grow = correctionWs.Range("D" & Rows.Count).End(xlUp).Row
lrow = ws.Range("D" & Rows.Count).End(xlUp).Row

For g = 3 To grow
    ' 检查当前循环行的E列是否为空
    If correctionWs.Range("E" & g) <> "" Then
        For r = 2 To lrow
            If ws.Range("D" & r) = correctionWs.Range("D" & g).Value And _
               ws.Range("Z" & r) = correctionWs.Range("C" & g).Value Then
                ws.Range("Z" & r) = correctionWs.Range("E" & g).Value
            End If
        Next r
    End If
Next g

End Sub

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