Formula =R[-1]C-RC[-1] by vba subtracting gives error

[MKLAQ]

hello experts
I need help about this formula I have this data but when I run it gives me error I try modified but I failed
the subtracting should begins c2-b3 and you can see the picture1 how subtracting but it gives error in picture to so I need help
picture1

item	B	c
		5000
1	400	4600
2	300	4300
3	500	3800
4	600	3200

picture2

item	B	c
		#VALUE!
1	400	#VALUE!
2	300	#VALUE!
3	500	#VALUE!
4	600	#VALUE!

thanks

 

[AlphaFrog]

The formula in cell C2 throws an error because there is text above in cell C1. The other formulas throw an error because the cell in above has an error.

 

[MKLAQ]

thanks I know what you said , can you guide me how I can modify

Sub Macro()

    Dim lr As Long

    lr = Cells(Rows.Count, "B").End(xlUp).Row
    Range("C2:C" & lr).FormulaR1C1 = "=R[-1]C-RC[-1]"

End Sub

 

[Joe4]

I think you want to begin your formula on row 3, not row 2, right (as C2 contains the hard-coded value 5000)?
If that is the case, your formula in your code shoudl be:

    Range("C3:C" & lr).FormulaR1C1 = "=R[-1]C-RC[-1]"

 

[MKLAQ]

@Joe4 I'm not sure if I did it , but I think I did it , anyway many thanks for solve my problem

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[MKLAQ]

@Joe4 may you mode the code ,please? I added another column it should the add column b to the balance in columnd and subtracting from column c can you follow the formula as in my picture

item	B		c
			3000
1	100	200	2900
2	200	400	2700
3	300	700	2300
4	100	800	1600

 

[Joe4]

If you are finding the RC reference confusing, there is a little trick you can do to make Excel figure it out for you.
Simply turn on your Macro Recorder, and record yourself typing in one of the formula you want on the worksheet in the normal format you are used to (i.e. =D2+B3-C3).
Then stop the Macro Recorder and view the code that you just recorded. The formula part is the VBA code you need to insert into your existing code. So just copy and paste that part into your code.

Note that if you inserted a column to move your formula over one column, that you will them need to adjust that, i.e. this part:

Range("C3:C" & lr)

will probably need to change to this:

Range("D3:D" & lr)

 

[MKLAQ]

I record the macro and I got the formula RC1 and I modified my code but it gives me error method range of object global failed in the line contain the formula
this is my code

Sub Macro()

    Dim lr As Long

    lr = Cells(Rows.Count, "D").End(xlUp).Row
    Range("D3:D & lr").FormulaR1C1 = "=R[-1]C+RC[-1]-RC[-1]"
End Sub

 

[Joe4]

Why did you move the quotes on the range part?
You have:

Range("D3:D & lr")

It should be:

Range("D3:D" & lr)