REFERENCE PROBLEM

abeed

Board Regular
Joined
Nov 19, 2005
Messages
79
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
Thanks in advance
 

Some videos you may like

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.

abeed

Board Regular
Joined
Nov 19, 2005
Messages
79
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
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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
Joined
Nov 19, 2005
Messages
79
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Nov 19, 2005
Messages
79
Thanks Richard. Oh God Its working at last. Its been a great help.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,383
Messages
5,571,818
Members
412,420
Latest member
Quintankerus
Top