# 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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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

Replies
0
Views
123
Replies
2
Views
124
Replies
4
Views
178
Replies
1
Views
58
Replies
2
Views
83