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
 

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.

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,884
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.
 
Master Excel Bundle

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.

Forum statistics

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