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

#### MKLAQ

##### Board Regular
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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### AlphaFrog

##### MrExcel MVP
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

##### Board Regular
thanks I know what you said , can you guide me how I can modify
VBA Code:
``````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:
Rich (BB code):
``    Range("C3:C" & lr).FormulaR1C1 = "=R[-1]C-RC[-1]"``

#### MKLAQ

##### Board Regular

@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

##### Board Regular

@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:
VBA Code:
``Range("C3:C" & lr)``
will probably need to change to this:
VBA Code:
``Range("D3:D" & lr)``

#### MKLAQ

##### Board Regular
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
VBA 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:
VBA Code:
``Range("D3:D & lr")``
It should be:
VBA Code:
``Range("D3:D" & lr)``

Replies
4
Views
90
Replies
11
Views
134
Replies
2
Views
94
Replies
2
Views
194
Replies
6
Views
147

1,127,802
Messages
5,626,975
Members
416,213
Latest member
neflerine

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back