excel - ClearContents 会影响其旁边的其他单元格中的公式

Question

当我ClearContent在一个单元格上使用它旁边的其他单元格使用公式时，它会更改其他单元格中的公式。例如：

.Range("L" & x2 & ":M" & x2).ClearContents  

我现在在 N2 有公式=IFERROR(F3/G3*3600,0)，我需要它留下来=IFERROR(F2/G2*3600,0)，下一行 N3 现在是=IFERROR(#REF!/#REF!*3600,0)

实际代码：

    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Dim LstRw As Long, lookup_rng As Range, x
    Dim Sht3 As Worksheet, Sht4 As Worksheet
    Dim LstRw2 As Long, lookup_rng2 As Range, x2

    Set Sht1 = Sheets("Pick Ticket Schedule")
    Set Sht2 = Sheets("Pick Ticket (History)")
    Set lookup_rng = Sht2.Range("B2:B10")

    With Sht1
        LstRw = .Cells(.Rows.Count, "T").End(xlUp).Row
        For x = LstRw To 2 Step -1
        'MsgBox "LstRw:" & LstRw & " " & x & "-" & .Cells(x, 20).Value & " -->" & lookup_rng.Value
            If Not lookup_rng.Find(what:=.Cells(x, 20), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
                Sht1.Range("J" & x & ":K" & x).ClearContents 'Shift:=xlUp <-- Only used for delete 'Need to delete value in cell J & K for the row that matches
                Sht1.Range("T" & x).ClearContents
            End If
        Next x
    End With
    
    Set Sht3 = Sheets("Master Production Schedule")
    Set Sht4 = Sheets("Historical FG")
    Set lookup_rng2 = Sht4.Range("B2:B10")

    With Sheets("Master Production Schedule")
        LstRw2 = .Cells(.Rows.Count, "V").End(xlUp).Row
        For x2 = LstRw2 To 2 Step -1
            If lookup_rng2.Find(what:=.Cells(x2, 22), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
                .Range("L" & x2 & ":M" & x2).ClearContents  'Need to delete value in cell L & M for the row that matches
                .Range("T" & x2).ClearContents
                .Range("V" & x2).ClearContents
            End If
        Next x2
    End With

具有单元格 O2 和 O3 的 O 列也以类似的方式受到影响，我假设这 2 列受到 L 和 M 列的某种影响。只有第 2 行和第 3 行有这些列的数据。最好的方法是什么使它不会影响以下行？提前致谢！-保罗

标签： excelvba