REFERENCE PROBLEM

[abeed]

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

 

[anthonya2369]

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

 

[abeed]

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]

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]

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:

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

giving G - B as I believe you want.

Best regards

Richard

 

[abeed]

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]

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

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:

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

should you need to.

Best regards

Richard

 

[abeed]

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