# REFERENCE PROBLEM

#### abeed

##### Board Regular
Hi There:
I have data like:
A------B-----C----D-----E----F----G H I
1990--1-----------3---------6-----8
1991--1-----------9---------2-----7
1992--2-----------5---------7-----1

My C col will have data diff(G1990-B1991).That means for previous year.
I was trying:
range("c2:c3").formular1c1 = "=RC[4].offset(0,-1) - RC[-1]"
--What is the error

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### anthonya2369

##### Active Member
try range c2 or c3 and not both and see if you get the same error or any error.

#### abeed

##### Board Regular
Hi Anthony:
If you are still up there.Still having error even for range(c3").
Application error.
I need to have:
cell(2,3).value = cell(2,3).offset(-1,3).value - cell(2,3).offset(0,-1).value
cell(3,3).value = cell(3,3).offset(-1,3).value - cell(3,3).offset(0,-1).value

How can i do it using a range and a formula.

Thanks

#### sykes

##### Well-known Member
Hi Abeed

Can you just state clearly what you are trying to achieve.

Are you trying to just put a formula in your C column to subtract the value in one cell from the value in another cell?

For example, are you trying to put in C2 the result of taking the B column value corresponding to the row in which 1991 appears in the A column, from the G / A1990 value?

If so we need to know your Row numbers! in other words which ROWS are 1990, 1991, 1992 etc etc in?

If we assume that your data starts at the very top of the sheet, then the 1990 value will be in cell A1. i.e. ROW1.

Continuing with these assumptions, do the following.

Click into C1 - the C cell corresponding to 1990.
Click into the long white space above the grey ABCDE line i.e. the "Formula bar" and type (or copy and paste) the following:

=G1-B2

Either click the green tick to the left, or just hit the return key on your keyboard.

I'm hoping this will give you the result you expected in C1, i.e. 7, if I've understood your question correctly.

Use the same principal for your other cells if this has done what you wanted.

If not, come back and try and state more clearly what it is you're trying to achieve. Then we'll try and help.

#### Richard Schollar

##### MrExcel MVP

I don't see why you are using the Offset in your formula (which would produce an invalid Excel formula - hence the error)? It works without it:

Code:
``range("c2:c3").FormulaR1C1 = "=RC[4] - RC[-1]"``

giving G - B as I believe you want.

Best regards

Richard

#### abeed

##### Board Regular
Hi Richard:
Sorry for the delay.
A------B-----C----D-----E----F----G H I
1990--1-----------3---------6-----8
1991--1-----------9---------2-----7
1992--2-----------5---------7-----1

I want to subtract B data of 1991 from G data of 1990 and put the reult in C col of 1991. Means B data of current year subtract from G data of previous year.
cell(2,3).value = cell(2,3).offset(-1,4).value - cell(2,3).offset(0,-1).value.
For so many cells, I want to use a range and a formula.

Thanks

#### Richard Schollar

##### MrExcel MVP
Ah, apologies Abeed - I had misunderstood! Does this work:

Code:
``range("c3").FormulaR1C1 = "=R[-1]C[4] - RC[-1]"``

so this formula will reference information contained in both rows 2 and 3. You can highlight a range say:

Code:
``range("C3:C100").FormulaR1C1 = "=R[-1]C[4] - RC[-1]"``

should you need to.

Best regards

Richard

#### abeed

##### Board Regular
Thanks Richard. Oh God Its working at last. Its been a great help.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,999
Messages
5,834,813
Members
430,323
Latest member
Regash

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