how fix error #VALUE! & #REF! when using FormulaR1C1

[Alaa mg]

Hi experts,
I try subtracting using FormulaR1C1 should be E2= C2-D2 when fill values in column C or D and after E2 the E3=E2+C3-D3
but it gives error in E1=#VALUE! and E2:E3= #REF! .
how can I fix it, please?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 3 And Target.Row > 1 Or Target.Column = 4 And Target.Row > 1 Then
lr = Range("a" & Rows.Count).End(xlUp).Row
Range("E2").FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("E2").Value = Range("E2").Value
Range("E3:E" & lr).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
Range("E3:E" & lr).Value = Range("E3:E" & lr).Value
End If
Application.EnableEvents = True
End Sub

 

[DanteAmor]

just curiosity , I wanted do that by Formula R1C1 because I use record macro and try to learn how work this formula .

In that case But I do not recommend you complicate yourself with the RC formula. But for illustration purposes:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Or Target.Row < 2 Then Exit Sub
  Dim lr As Long
  
  Application.EnableEvents = False
  If Not Intersect(Target, Range("C:D")) Is Nothing Then
    lr = Range("A:E").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    With Range("E2")
      .FormulaR1C1 = "=RC[-2]-RC[-1]"
      .Value = .Value
    End With
    If lr > 2 Then
      With Range("E3:E" & lr)
        .FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
        .Value = .Value
      End With
    End If
  End If
  Application.EnableEvents = True
End Sub

 

[Alaa mg]

it looks like you may be entering in values for column C OR column D, but NOT always both.

that's correct .

 

[Alaa mg]

@DanteAmor
Awesome !
based on your advice I will take first
thank you so much for two options .