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

 

[Joe4]

Do you mean to be looking at column A in order to determine where the last row of data actually exists?

lr = Range("a" & Rows.Count).End(xlUp).Row

The only way I can see cell E1 being updated is if there is either no data in column A, or the last row with data in column A is row 1.
Then this part of your code:

Range("E3:E" & lr)

would be trying to populate E1:E3.

 

[Alaa mg]

Hi Joe
the data are A:E
the headers in row1 , my goal when fill numeric values in columns C or D then should calculate in column E

 

[Joe4]

Does it work better if you change:

lr = Range("a" & Rows.Count).End(xlUp).Row

to this:

lr = Range("C" & Rows.Count).End(xlUp).Row

 

[DanteAmor]

Try this:

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
    Range("E2").Value = Range("C2").Value - Range("D2").Value
    If lr > 2 Then
      With Range("E3:E" & lr)
        .Formula = "=E2+C3-D3"
        .Value = .Value
      End With
    End If
  End If
  Application.EnableEvents = True
End Sub

 

[Alaa mg]

Joe
in reality I have ever tested but doesn't make difference.

 

[Joe4]

In that case we need to see EXACTLY what you data looks like at the start (before you enter anything), and then tell us exactly what you are entering and in which cells, so we can recreate your scenario.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Alaa mg]

@DanteAmor
great ! this solves the problem .
just curiosity , I wanted do that by Formula R1C1 because I use record macro and try to learn how work this formula .
thanks

​

 

[Alaa mg]

@Joe if you still interest and could do that by fFormualaR1C1 despite of it's solved by DanteAmore
I put the values in columns C,D and how should calculate in column E

copy amount to columns v0 a.xlsm
	A	B	C	D	E
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE
2	22/05/2023 19:55:50	bbbb	5000	2,000.00	3,000.00
3	22/05/2023 19:55:56	bbbb	2000		5,000.00
4				100.00	4,900.00
voucher

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E4	E3	=E2+C3-D3

 

[Joe4]

OK, based on your image I think I see what the "key" piece that was not clear from the original post. For certain rows, it looks like you may be entering in values for column C OR column D, but NOT always both.

That is why it is good to post examples. What you show often fills in some of the important details that were left out (sometimes the person posting the question isn't always aware how some seemingly minor details can affect the problem greatly).