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]

sorry I don't note about quotes , but the formula doesn't show in column d correctly as I recorded in macro it shows in column d like this =D2+C3-C3 it supposes =D2+B3-C3 , may you see what I got ,please?

item	B		c
			3000
1	100	200	3000
2	200	400	3000
3	300	700	3000
4	100	800	3000

 

[Joe4]

sorry I don't note about quotes , but the formula doesn't show in column d correctly as I recorded in macro it shows in column d like this =D2+C3-C3 it supposes =D2+B3-C3 , may you see what I got ,please?

You didn't need to move the quotes around at all in the original formula. All you had to do was replace the "C"s with "D"s.

Sounds like you might have entered the wrong formula in when you were recording it.
It should be:

Range("D3:D" & lr).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"

 

[MKLAQ]

I've found the wrong this value +RC[-1]

"=R[-1]C+RC[-1]-RC[-1]"

the right is as your formula +RC[-2]

"=R[-1]C+RC[-2]-RC[-1]"

despite I see formula RC is complicated for me but it's a great for many math , I was searching in the internet for this way some codes do that by loop and it contains many lines surprisingly I've found this short code do that quickly without looping makes the code is slow , by the way the code in my post is yours I've found it in one of sharing in this forum

many thanks for your time and assistance

 

[MKLAQ]

finally I would ask you if it is possible hide the formula without protect by add some line to code to do that

 

[Joe4]

You are welcome.

Here is a good explanation of the R1C1 references, and how they work: Excel Formulas - A1 or R1C1 Notation
They are nice in that it works relative to whatever cell you are in, which is why you can apply it to a whole range of cells at once (which helps to avoid unnecessary loops).

Quite frankly, they can be a little daunting/confusing when you first look at them. That is where the Macro Recorder comes in handy. Just enter the formula normally, and the Macro Recorder will record it in R1C1 notation for you automatically.
The Macro Recorder is a very underrated tool. You can use it to get all sorts of code snippets like this (you don't need to memorize all this stuff!).

 

[Joe4]

finally I would ask you if it is possible hide the formula without protect by add some line to code to do that

Can you expand upon this?
I am not quite sure whay you mean/what you are after.

 

[MKLAQ]

I mean showing the value without formula d2+b3-c3
and I'm sorry say that the formula is still problem if I add a new values the formula doesn't work , do you have any idea ?

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

this is my file
FORMULA RC1

 

[Joe4]

I mean showing the value without formula d2+b3-c3

So, do you want to convert the formulas to hard-coded values?
You can do this, but then if you change any of the underlying values (i.e. B3 or C3), the value won't update automatically.
If you are not going back and changing existing data this won't be an issue. But if you are, you either need to leave the formula, or use different VBA code that is automatically triggered upon data changes (event procedure VBA code).

By the way, what is the concern with leaving the formulas?

and I'm sorry say that the formula is still problem if I add a new values the formula doesn't work , do you have any idea ?

That is to be expected. The VBA code is only running on existing data at the time it was run. So if you add new data after that, you would either need to run the VBA code again, or change the kind of VBA code you are using to event procedure VBA code, that runs automatically as new data is entered.

It seems that you have been slowly revealing new information about this issue as we go along in this post. This is not the most efficient way to do things. It is best to carefully analyze and think about nall that you are looking for, and let us know that up front. That way we can devise a solution that will work for you instead of having to change things because the original path will not work for these other details you didn't mention initially.

 

[MKLAQ]

thanks Joe for your notices

so about the hide the formula actually I back to update or change the values so I want updating the values automatically without show the formula in column d

what I want if it's possible I would change the values in column d if I change the values in column b,c automatically by vba because I have at least 10000 rows of data if you see the information is poor please inform me

note : about you suggestion run macro again if I add a new data honestly it doesn't work at all i tried more than one time

and when I clear the data and start again from row 3 it gives error I no know where I mistook

 

[Joe4]

OK, I think I have some event procedure code that should do what you want.
This code runs automatically, but MUST be placed in the correct spot, and its procedure name must NOT be altered in any way.

It goes in the corresponding sheet module. The easiest way to make sure you are putting it in the correct place is to do the following:
1. Go to the sheet that you want to apply this to
2. Right-click on the Sheet tab name at the bottom of the screen
3. Select "View Code"
4. Paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long

'   Run whenever a change is made to the starting value in cell D2
'   or a change is made in columns B or C
    If Target.Address = Range("D2").Address Or Target.Column = 2 Or Target.Column = 3 Then
'       Temporarily disable events while code runs
        Application.EnableEvents = False
'       Find last row with data in column B
        lr = Cells(Rows.Count, "B").End(xlUp).Row
'       Populate formula in column D starting in row 3 and going to the end
        Range("D3:D" & lr).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
'       Convert formulas to hard-coded values
        Range("D3:D" & lr).Value = Range("D3:D" & lr).Value
'       Reenable events while code runs
        Application.EnableEvents = True
    End If

End Sub

This code will run automatically whenever the starting value is altered in cell D2, and also runs whenever any data is changed/added to columns B or C.
I have added lots of comments to explain what each step is doing.
Note that no formulas are visible on the sheet, just their resulting values.