我写了一个工作表更改事件脚本，当“状态”列更改为“完成”时，将一个单元格区域复制到工作表上的新区域。在测试过程中，我注意到当脚本运行时，C列被跳过，就好像它不存在一样。
对变量进行监视表明B的值在被清除之前一直存在，然后在'For Each'循环的下一次迭代中，变量的值来自列D。我试着创建一个新的工作簿，并将代码添加到一个新的工作表，同样的事情发生了。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
 
    Dim dlr As Long
    Dim r As Long
    Dim nr As Long
    Dim lr As Long
    Dim mAValue As Variant
    Dim mARange As Range
    Dim wsIW As Worksheet
    Set wsIW = Worksheets("IW Phase")
 
    If Target.Column = 14 And Target.Row > 12 Then ' 14 means Column N, modify it as per your need
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        r = Target.Row
        lr = 499
        nr = 500
        dlr = wsIW.Cells(Rows.Count, "AB").End(xlUp).Row + 1
       
        Dim Ans As VbMsgBoxResult
        Dim cRange As Range
        Set cRange = wsIW.Range("B" & r & ":O" & r)
 
        If Target.Value = "Review complete, actions closed" Then
            Ans = MsgBox("Action will be moved to the Closed Actions area." & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & "                        Are you sure?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm Action Close")
            If Ans = vbYes Then
            wsIW.Cells(dlr, 42).Value = Date
                For Each cell In cRange
                    If cell.MergeCells Then
                        mAValue = cell.MergeArea.Value
                        Set mARange = cell.MergeArea
                        wsIW.Cells(dlr, cell.Column).Offset(0, 26).Value = mAValue
                        mARange.UnMerge
                        cell.Delete xlShiftUp
                        Set mARange = mARange.Resize(mARange.Rows.Count)
                        mARange.Merge
                        mARange.Cells(1, 1).Value = mAValue
                    Else:
                        wsIW.Cells(dlr, cell.Column).Offset(0, 26).Value = cell.Value
                        cell.Delete xlShiftUp
                    End If
                Next cell
            Else: Target.Value = ""
            Application.EnableEvents = True
            Exit Sub
            End If
        End If
 
        Application.ScreenUpdating = True ' Re-enable screen updating
        wsIW.Range("B" & lr & ":O" & lr).Copy wsIW.Range("B" & nr & ":O" & nr)
 
    End If
 
    Application.EnableEvents = True ' Always re-enable events
End Sub

字符串
我已经尝试解决这个问题，但我实际上不知道它是什么/为什么会发生。我曾试图通过隐藏C列来解决问题，但这会带来一系列问题，通常不是一个好的解决方案。任何帮助将不胜感激