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

MKLAQ

Board Regular
Joined
Jan 30, 2021
Messages
108
Office Version
  1. 2016
Platform
  1. Windows
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
itemBc
5000
14004600
23004300
35003800
46003200


picture2
itemBc
#VALUE!
1400#VALUE!
2300#VALUE!
3500#VALUE!
4600#VALUE!

thanks
 

Some videos you may like

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
Joined
Sep 2, 2009
Messages
16,400
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
Joined
Jan 30, 2021
Messages
108
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,687
Office Version
  1. 365
Platform
  1. Windows
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]"
 
Solution

MKLAQ

Board Regular
Joined
Jan 30, 2021
Messages
108
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,687
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help.
 

MKLAQ

Board Regular
Joined
Jan 30, 2021
Messages
108
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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
itemBc
3000
11002002900
22004002700
33007002300
41008001600
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,687
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 30, 2021
Messages
108
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,687
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top