vba - 将更改行位置的公式应用于VBA

Question

我有一个工作表，用于计算 A 列中的指定日期和 B 列中的 today() 日期之间的天数，如果 D 列中有“CLOSED”一词，则停止 C 列中的计数。但我有一个问题如果 D 列再次为空白，我想重新应用公式。我不确定如何使列行出现在要使用的公式的正确位置

下面是 VBA 代码:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells = "CLOSED" Then
'Run only when change is made in Column D
  If Target.Column = 4 Then
   Application.EnableEvents = False
'Replace the formula with the current result
    Range("C" & Target.Row) = Range("C" & Target.Row).Value
    Range("B" & Target.Row) = Range("B" & Target.Row).Value
   Application.EnableEvents = True
  End If
 End If

  If Target.Cells = "" Then
'Run only when change is made in Column D
  If Target.Column = 4 Then
   Application.EnableEvents = False
'Replace the formula with the current result
    Range("C" & Target.Row).Formula = "=TRUNC($B2 - $A2)"
    Range("B" & Target.Row).Value = "=Today()"
   Application.EnableEvents = True
  End If
 End If
End Sub

如果有人能教我如何正确更改代码，我将不胜感激:

Range("C" & Target.Row).Formula = "=TRUNC($B2 - $A2)"

因为我还是 VBA 编程的新手，想从我的错误中吸取教训

Answer 1

下面会做你想做的。了解您可以使用 .FormulaR1C1类似于向上/向下填充的效果。包含超过 1 个单元的潜在问题已更改。没有检查 A/B 列中的单元格是否为空。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oRng As Range

    Application.EnableEvents = False
    For Each oRng In Target.Cells
        With oRng
            If .Column = 4 Then
                If UCase(Trim(.Value)) = "CLOSED" Then
                    .Worksheet.Cells(.Row, "B").Value = .Worksheet.Cells(.Row, "B").Value
                    .Worksheet.Cells(.Row, "C").Value = .Worksheet.Cells(.Row, "C").Value
                ElseIf Len(Trim(.Value)) = 0 Then
                    .Worksheet.Cells(.Row, "B").Formula = "=Today()"
                    .Worksheet.Cells(.Row, "C").FormulaR1C1 = "=TRUNC(RC[-2]-RC[-3])"
                End If
            End If
        End With
    Next oRng
    Application.EnableEvents = True

End Sub