I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent to
where the total is required.) The formula can not be turned into a fixed
offset but must be a range from the two points. Help!

ActiveCell.FormulaR1C1="=sum(R[" & subrow1 & "]C[" & subcol1 & "]):R[" &
subrow2 & "]C[" subcol2 & "])"

Not tested but follow this style!

Regards,
Stefi


€žSue€� ezt Ã*rta:

I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent to
where the total is required.) The formula can not be turned into a fixed
offset but must be a range from the two points. Help!

Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined
error.
What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and
ActiveCell is B4 is to put the formula = sum(i4:i10) in B4.

"Stefi" wrote:

ActiveCell.FormulaR1C1="=sum(R[" & subrow1 & "]C[" & subcol1 & "]):R[" &
subrow2 & "]C[" subcol2 & "])"

Not tested but follow this style!

Regards,
Stefi


€žSue€� ezt Ã*rta:

I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent to
where the total is required.) The formula can not be turned into a fixed
offset but must be a range from the two points. Help!

ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" &
subrow2 & "]C[" & subcol2 & "])"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sue" wrote in message
...
I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent to
where the total is required.) The formula can not be turned into a fixed
offset but must be a range from the two points. Help!

Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined
error.
What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and
ActiveCell is B4 is to put the formula = sum(i4:i10) in B4.

"Bob Phillips" wrote:


ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" &
subrow2 & "]C[" & subcol2 & "])"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sue" wrote in message
...
I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent to
where the total is required.) The formula can not be turned into a fixed
offset but must be a range from the two points. Help!

I have the same problem!
From the immediate window I can fill the formula and it works fine, but from a Sub I get Application-defined error.
I have use intermediate text strings, displayed them before putting them in a worksheet, compared them with the formula bar
(switched to R1C1 reference style) ............
Can't get it done thru code!

Please keep us informed here about any progress made


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Sue" wrote in message ...
| Thankyou but I still have the same problem. It compiles but won't run. It
| comes up with Run-time error 1004 - Application-defined or object-defined
| error.
| What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and
| ActiveCell is B4 is to put the formula = sum(i4:i10) in B4.
|
| "Bob Phillips" wrote:
|
|
| ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" &
| subrow2 & "]C[" & subcol2 & "])"
|
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my addy)
|
|
|
| "Sue" wrote in message
| ...
| I need to enter a sum formula by code.
| I can establish the coordinates of the range in R1C1 form.
| Say the points are (subrow1,subcol1) and (subrow2,subcol2).
| I can get where I want to put the formula.
| I have tried:
| ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
| or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
| - as the column is adjacent - and variations on the theme but everything
| with a variable in it produces a run time error.
| I need the total of a variable amount of the column (which is adjacent to
| where the total is required.) The formula can not be turned into a fixed
| offset but must be a range from the two points. Help!
|
|
|
|

This works for me

Dim mpFormula As String

mpFormula = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]:R[" & _
subrow2 & "]C[" & subcol2 & "])"
ActiveCell.FormulaR1C1 = mpFormula


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sue" wrote in message
...
Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined
error.
What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and
ActiveCell is B4 is to put the formula = sum(i4:i10) in B4.

"Bob Phillips" wrote:


ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R["
&
subrow2 & "]C[" & subcol2 & "])"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sue" wrote in message
...
I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but
everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent
to
where the total is required.) The formula can not be turned into a
fixed
offset but must be a range from the two points. Help!

Hi Bob, thanks very much for your help. I have worked hard on this problem
and I got a result (the correct answers) with this code:

Range("A1").Offset(subrow1 - 1, subcol1).Select
mpFormula = "=SUM(R[" & subrow1 - subrow1 & "]C[" & subcol1 - _

subcol1 - 1 & "]:R[" & subrow2 - subrow1 & "]C[" &
subcol2 - _
subcol1 - 1 & "])"
ActiveCell.FormulaR1C1 = mpFormula

BUT I have established subrow1, subcol1, etc as R1C1 coordinates of cells I
want to reference and Excel seems only to use these as offsets. This makes
for very clumsy and obscure code. Is there a way I can go to
R[subrow1]C[subcol1+1] and put the formula
sum(R[subrow1]C[subcol1]:R[subrow2]C[subcol2]). Does this need an absolute
reference? I've tried but cannot get the syntax right.


"Bob Phillips" wrote:

This works for me

Dim mpFormula As String

mpFormula = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]:R[" & _
subrow2 & "]C[" & subcol2 & "])"
ActiveCell.FormulaR1C1 = mpFormula


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sue" wrote in message
...
Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined
error.
What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and
ActiveCell is B4 is to put the formula = sum(i4:i10) in B4.

"Bob Phillips" wrote:


ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R["
&
subrow2 & "]C[" & subcol2 & "])"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sue" wrote in message
...
I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but
everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent
to
where the total is required.) The formula can not be turned into a
fixed
offset but must be a range from the two points. Help!

maybe...

myformula = "=sum(r[" & subrow1 & "]c[" & subcol1 _
& "]:r[" & subrow2 & "]c[" & subcol2 & "])"

But I'm kind of confused about what you really want.




Sue wrote:

Hi Bob, thanks very much for your help. I have worked hard on this problem
and I got a result (the correct answers) with this code:

Range("A1").Offset(subrow1 - 1, subcol1).Select
mpFormula = "=SUM(R[" & subrow1 - subrow1 & "]C[" & subcol1 - _

subcol1 - 1 & "]:R[" & subrow2 - subrow1 & "]C[" &
subcol2 - _
subcol1 - 1 & "])"
ActiveCell.FormulaR1C1 = mpFormula

BUT I have established subrow1, subcol1, etc as R1C1 coordinates of cells I
want to reference and Excel seems only to use these as offsets. This makes
for very clumsy and obscure code. Is there a way I can go to
R[subrow1]C[subcol1+1] and put the formula
sum(R[subrow1]C[subcol1]:R[subrow2]C[subcol2]). Does this need an absolute
reference? I've tried but cannot get the syntax right.

"Bob Phillips" wrote:

This works for me

Dim mpFormula As String

mpFormula = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]:R[" & _
subrow2 & "]C[" & subcol2 & "])"
ActiveCell.FormulaR1C1 = mpFormula


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sue" wrote in message
...
Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined
error.
What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and
ActiveCell is B4 is to put the formula = sum(i4:i10) in B4.

"Bob Phillips" wrote:


ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R["
&
subrow2 & "]C[" & subcol2 & "])"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sue" wrote in message
...
I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form.
Say the points are (subrow1,subcol1) and (subrow2,subcol2).
I can get where I want to put the formula.
I have tried:
ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2])
or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1])
- as the column is adjacent - and variations on the theme but
everything
with a variable in it produces a run time error.
I need the total of a variable amount of the column (which is adjacent
to
where the total is required.) The formula can not be turned into a
fixed
offset but must be a range from the two points. Help!








--

Dave Peterson