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?
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?
VBA Code:
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