Use of Variable in Sum

[Sue]

I have worked out with a count loop how many lines need to be added.
This is called i and can be from 1 to 10 usually.
This is the formula I have tried to use. Without the variable it works.
I conclude that brackets, quotes and ampersands are my problem and not
for the first time either! Can someone help me to get the variable accepted
in the formula? Help!
eg i=3
ActiveCell.Range("d1").FormulaR1C1 = "=-Sum(R[& i]C:R[-1]C)"

This should sum the 3 cells above d1 and put the answer in d1. It needs
debugging.

 

[Jacob Skaria]

It should be "=-Sum(R[" & i & "]C:R[-1]C)"

Are you sure it is

ActiveCell.Range("d1").
OR
ActiveSheet.Range("D1").

But are you sure it is

If this post helps click Yes

 

[Patrick Molloy]

range("D1") IS row 1, so there cannot be three rows above it
Do you mean that D is the sum of A-C?

"=SUM(RC1:RC3)"

this works in column D - no need to adjust

For the sum to go in any other column (not A,B or C) then
"=SUM(RC[-3]:RC[-1])"

 

[Sue]

Congratulations on your Silver. I see you just got it today! You have helped
me recently with another quote and ampersand problem - I so appreciate it. I
realise I do not fully grasp when to use quotes, ampersands and brackets. I
did try the quotes but I did not have the second ampersand.
By the way my i was a negative i to clarify working on the formula - one
less sign (i=-i)! I have tested it in my program and gone back to i being +ve
with -i in the formula and it does work exactly as it is meant. The cursor
remains on the left and the group of figures are in column d immediately
above the cursor row. It is relative, the actual row is not row 1.

It should be "=-Sum(R[" & i & "]C:R[-1]C)"

Are you sure it is

ActiveCell.Range("d1").
OR
ActiveSheet.Range("D1").

But are you sure it is

If this post helps click Yes
---------------
Jacob Skaria

I have worked out with a count loop how many lines need to be added.
This is called i and can be from 1 to 10 usually.
This is the formula I have tried to use. Without the variable it works.
I conclude that brackets, quotes and ampersands are my problem and not
for the first time either! Can someone help me to get the variable accepted
in the formula? Help!
eg i=3
ActiveCell.Range("d1").FormulaR1C1 = "=-Sum(R[& i]C:R[-1]C)"

This should sum the 3 cells above d1 and put the answer in d1. It needs
debugging.

 

[Jacob Skaria]

Thanks Sue. I have tried to explain a bit about strings and ampersands. Hope
it helps.. If not post back

A. Quotes represents string values..

If 'i' is a variable the formula will be
"=-Sum(R[" & i & "]C:R[-1]C)"
When you have two variables used here say i and y
"=-Sum(R[" & i & "]C:R[" & y & "]C)"

If you break it down it is easy to understand. All strings are combined
using ampersands.

"=-Sum(R[" .......is a string and so surrounded by quotes
i ............is a variable
"]C:R[" ......is again a string and so surrounded by quotes
y .................another variable
"]C)" ..........yet another string

B. Ampersands are used to combine variables OR strings...

C. Use .Offset to refer relative values like ActiveCell.Offset
Activecell.Offset(2,3) will refer to 2 rows down and 3 columns to the right

D. ActiveCell.Offset(0,3).FormulaR1C1 = "=Sum(R[" & -3 & "]C:R[-1]C)"
will put in a formula which adds up 3 rows to the top.

You dont need a negative sign placed before the formula..If i is negative
and suppose value is -3 the below formula will add up 3 rows above...
i = -3
"=Sum(R[" & i & "]C:R[-1]C)"

If this post helps click Yes
---------------
Jacob Skaria

Congratulations on your Silver. I see you just got it today! You have helped
me recently with another quote and ampersand problem - I so appreciate it. I
realise I do not fully grasp when to use quotes, ampersands and brackets. I
did try the quotes but I did not have the second ampersand.
By the way my i was a negative i to clarify working on the formula - one
less sign (i=-i)! I have tested it in my program and gone back to i being +ve
with -i in the formula and it does work exactly as it is meant. The cursor
remains on the left and the group of figures are in column d immediately
above the cursor row. It is relative, the actual row is not row 1.

It should be "=-Sum(R[" & i & "]C:R[-1]C)"

Are you sure it is

ActiveCell.Range("d1").
OR
ActiveSheet.Range("D1").

But are you sure it is

If this post helps click Yes
---------------
Jacob Skaria

I have worked out with a count loop how many lines need to be added.
This is called i and can be from 1 to 10 usually.
This is the formula I have tried to use. Without the variable it works.
I conclude that brackets, quotes and ampersands are my problem and not
for the first time either! Can someone help me to get the variable accepted
in the formula? Help!
eg i=3
ActiveCell.Range("d1").FormulaR1C1 = "=-Sum(R[& i]C:R[-1]C)"

This should sum the 3 cells above d1 and put the answer in d1. It needs
debugging.

 

[Sue]

Thanks again. What you have said has helped a lot. I did not realise that
this was just a string. I thought quite wrongly the quotes applied to the
expression as a whole and I tried to ampersand variables into the centre!
It does have the -ve out front because this is the balancing entry of a
guided General Journal entry (ie) the overall total (what is there plus this)
is 0, and now it is working. Yeah!

Thanks Sue. I have tried to explain a bit about strings and ampersands. Hope
it helps.. If not post back

A. Quotes represents string values..

If 'i' is a variable the formula will be
"=-Sum(R[" & i & "]C:R[-1]C)"
When you have two variables used here say i and y
"=-Sum(R[" & i & "]C:R[" & y & "]C)"

If you break it down it is easy to understand. All strings are combined
using ampersands.

"=-Sum(R[" .......is a string and so surrounded by quotes
i ............is a variable
"]C:R[" ......is again a string and so surrounded by quotes
y .................another variable
"]C)" ..........yet another string

B. Ampersands are used to combine variables OR strings...

C. Use .Offset to refer relative values like ActiveCell.Offset
Activecell.Offset(2,3) will refer to 2 rows down and 3 columns to the right

D. ActiveCell.Offset(0,3).FormulaR1C1 = "=Sum(R[" & -3 & "]C:R[-1]C)"
will put in a formula which adds up 3 rows to the top.

You dont need a negative sign placed before the formula..If i is negative
and suppose value is -3 the below formula will add up 3 rows above...
i = -3
"=Sum(R[" & i & "]C:R[-1]C)"

If this post helps click Yes
---------------
Jacob Skaria

Congratulations on your Silver. I see you just got it today! You have helped
me recently with another quote and ampersand problem - I so appreciate it. I
realise I do not fully grasp when to use quotes, ampersands and brackets. I
did try the quotes but I did not have the second ampersand.
By the way my i was a negative i to clarify working on the formula - one
less sign (i=-i)! I have tested it in my program and gone back to i being +ve
with -i in the formula and it does work exactly as it is meant. The cursor
remains on the left and the group of figures are in column d immediately
above the cursor row. It is relative, the actual row is not row 1.

It should be "=-Sum(R[" & i & "]C:R[-1]C)"

Are you sure it is

ActiveCell.Range("d1").
OR
ActiveSheet.Range("D1").

But are you sure it is

If this post helps click Yes
---------------
Jacob Skaria


:

I have worked out with a count loop how many lines need to be added.
This is called i and can be from 1 to 10 usually.
This is the formula I have tried to use. Without the variable it works.
I conclude that brackets, quotes and ampersands are my problem and not
for the first time either! Can someone help me to get the variable accepted
in the formula? Help!
eg i=3
ActiveCell.Range("d1").FormulaR1C1 = "=-Sum(R[& i]C:R[-1]C)"

This should sum the 3 cells above d1 and put the answer in d1. It needs
debugging.