Formula2R1C1使创建相对公式变得容易。我通常不自己写Formula2R1C1公式。相反，我将一个普通公式添加到ActiveCell，然后将Formula2R1C1打印到即时窗口。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:C3")) Is Nothing Then
        On Error Resume Next
        Application.EnableEvents = False
        If Application.WorksheetFunction.CountA(Range("A2:C2")) = 0 Then
            Target.EntireRow.Range("D1").ClearContents
        Else
            Target.EntireRow.Range("D1").Formula2R1C1 = "=SUM(RC[-3]:RC[-1])"
        End If
        Application.EnableEvents = True
        On Error GoTo 0
    End If
End Sub

这将处理跨行的多单元格更新：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, rw As Range, c As Range, rngSum As Range
    Set rng = Application.Intersect(Target, Me.Range("A2:C100")) 'for example
    If Not rng Is Nothing Then
        For Each rw In rng.Rows 'check each changed row...
            Set rngSum = rw.EntireRow.Range("A1:C1") 'range is *relative* to `rw`
            Set c = rw.EntireRow.Columns("D")
            If Application.CountA(rngSum) = 0 Then
                c.ClearContents
            Else
                c.Formula = "=SUM(" & rngSum.Address(False, False) & ")"
            End If
        Next rw
    End If
End Sub