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

 

[MKLAQ]

well done ! that's exactly what I want but I note thing if in column b is empty then the code doesn't work it should the empty cell considers 0 and it supposes sum and subtracting without any problem ,can you fix it please ?

 

[Joe4]

well done ! that's exactly what I want but I note thing if in column b is empty then the code doesn't work it should the empty cell considers 0 and it supposes sum and subtracting without any problem ,can you fix it please ?

Remember, we are looking at column B to determine where our last row of data exists. So if the last row of data has nothing in column B, it is not going to include that row.
In determining where the last row of data exists, we want to choose a column that will ALWAYS have data for any row with data.
Will column A always have data for every row with data? If so, then we should change this line of code:

        lr = Cells(Rows.Count, "B").End(xlUp).Row

to this:

        lr = Cells(Rows.Count, "A").End(xlUp).Row

 

[MKLAQ]

that's great !! honestly I expect the code is complicated but is very simple for who knows write the code but for me absolutely is difficult because I have no basics to write code just I try modified what I find in the internet but write the code I can't , indeed I'm very lucky to find somebody answers my question it's not easy to find somebody write the code for me especially is professional like you
many thanks for a great assistance and explanations how the code works

 

[Joe4]

that's great !! honestly I expect the code is complicated but is very simple for who knows write the code but for me absolutely is difficult because I have no basics to write code just I try modified what I find in the internet but write the code I can't , indeed I'm very lucky to find somebody answers my question it's not easy to find somebody write the code for me especially is professional like you
many thanks for a great assistance and explanations how the code works

You are welcome.

I try to comment most of my code to kind of explain what is going on in each step, so that hopefully it makes some sense to you.
But if you have any particular questions about any of the lines of code, please do not hesitate to ask.
I like to try to help people learn and understand (not just get the answers).

 

[MKLAQ]

about post#17 this is my a big mistake when I specify who the last row should fill I specified the column d this is not logical because I don't fill in this column the code should work that ,I was specifying column a or b or c that what I fill

that's why it doesn't calculate a new data so you're extremely right about I should run the macro again if I add a new data I tested and works excellently , just I would share that with you to take advantage other members and of course varieties solutions it will be great

and this is my data and mod the code

item	B		c
			3000
1	11	200	2811
2	200	400	2611
3	300	700	2211
4	100	800	1511
5	101	901	711

Sub Macro()

    Dim lr As Long

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

this is work for me
note : you have to fill in column b to work the code you can change col a or c except d
and good look for every one