I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

dim s0 as string 'I'd use long's
dim s1 as string

s0 = "3"
s1 = "6"

somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])"

funmi_Bash wrote:

I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

--

Dave Peterson

Dave,
I would just like to say a very BIG 'Thank You" for this post.

Here's the working implementation for the benefit of everyone:

Range(Cells(5, 9), Cells(5, 9)).Formula = "=SUM(RC[" & a0 & "]:RC[" & a1
& "])"

Thanks again, Dave!

"Dave Peterson" wrote:

dim s0 as string 'I'd use long's
dim s1 as string

s0 = "3"
s1 = "6"

somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])"

funmi_Bash wrote:

I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

--

Dave Peterson
.

First, since you're using the formula in R1C1 reference style, you should be
using .FormulaR1C1.

Second,

Range(Cells(5, 9), Cells(5, 9))

represents a single cell.

You could either use:

cells(5,9).formulaR1C1 = ...
or even
range("I5").formular1c1 = ....



funmi_Bash wrote:

Dave,
I would just like to say a very BIG 'Thank You" for this post.

Here's the working implementation for the benefit of everyone:

Range(Cells(5, 9), Cells(5, 9)).Formula = "=SUM(RC[" & a0 & "]:RC[" & a1
& "])"

Thanks again, Dave!

"Dave Peterson" wrote:

dim s0 as string 'I'd use long's
dim s1 as string

s0 = "3"
s1 = "6"

somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])"

funmi_Bash wrote:

I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

--

Dave Peterson
.


--

Dave Peterson

Hello Dave!

I felt constrained to use the R1C1-reference style because the range style
did not give me the expected result, namely:
Range(Cells(5, 4 + 5), Cells(5, 4 + 5)).Formula = "=Sum(Range[Cells(5,
4), Cells(5, 4 + 5)])" does not work.

Had this worked, it would've been so much easier to just substitute the
figures with the appropriate algebraic variables/expressions.

"Dave Peterson" wrote:

First, since you're using the formula in R1C1 reference style, you should be
using .FormulaR1C1.

Second,

Range(Cells(5, 9), Cells(5, 9))

represents a single cell.

You could either use:

cells(5,9).formulaR1C1 = ...
or even
range("I5").formular1c1 = ....



funmi_Bash wrote:

Dave,
I would just like to say a very BIG 'Thank You" for this post.

Here's the working implementation for the benefit of everyone:

Range(Cells(5, 9), Cells(5, 9)).Formula = "=SUM(RC[" & a0 & "]:RC[" & a1
& "])"

Thanks again, Dave!

"Dave Peterson" wrote:

dim s0 as string 'I'd use long's
dim s1 as string

s0 = "3"
s1 = "6"

somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])"

funmi_Bash wrote:

I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

--

Dave Peterson
.


--

Dave Peterson
.

The point that I was trying to make was concerning the lefthand side of your
expression.

..formula should have been .formular1c1

And that this expression is too complex to refer to a single cell:
Range(Cells(5, 4 + 5), Cells(5, 4 + 5))


======
But to address your (new) point about using .formula, you could change the
expression to:

Cells(5, 4 + 5).Formula _
= "=sum(" & Range(Cells(5, 4), Cells(5, 4 + 4)).Address & ")"

Notice that I used .Formula (not .formular1c1).

..Address will return the A1 reference style address (with absolute columns and
rows)--like: $D$5:$H$5

There are options that you can use to eliminated the $ if you wanted.

I also had to change the ending cell to avoid a circular reference.

=====
But there are times when using R1C1 reference style (along with the .formular1c1
property) makes life much easier. This looks like it would be one of those
times.



funmi_Bash wrote:

Hello Dave!

I felt constrained to use the R1C1-reference style because the range style
did not give me the expected result, namely:
Range(Cells(5, 4 + 5), Cells(5, 4 + 5)).Formula = "=Sum(Range[Cells(5,
4), Cells(5, 4 + 5)])" does not work.

Had this worked, it would've been so much easier to just substitute the
figures with the appropriate algebraic variables/expressions.

"Dave Peterson" wrote:

First, since you're using the formula in R1C1 reference style, you should be
using .FormulaR1C1.

Second,

Range(Cells(5, 9), Cells(5, 9))

represents a single cell.

You could either use:

cells(5,9).formulaR1C1 = ...
or even
range("I5").formular1c1 = ....



funmi_Bash wrote:

Dave,
I would just like to say a very BIG 'Thank You" for this post.

Here's the working implementation for the benefit of everyone:

Range(Cells(5, 9), Cells(5, 9)).Formula = "=SUM(RC[" & a0 & "]:RC[" & a1
& "])"

Thanks again, Dave!

"Dave Peterson" wrote:

dim s0 as string 'I'd use long's
dim s1 as string

s0 = "3"
s1 = "6"

somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])"

funmi_Bash wrote:

I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson

Thanks again Dave!

What can I say? Beauty, eh?

God bless you my man! Real Good!!

"Dave Peterson" wrote:

The point that I was trying to make was concerning the lefthand side of your
expression.

..formula should have been .formular1c1

And that this expression is too complex to refer to a single cell:
Range(Cells(5, 4 + 5), Cells(5, 4 + 5))


======
But to address your (new) point about using .formula, you could change the
expression to:

Cells(5, 4 + 5).Formula _
= "=sum(" & Range(Cells(5, 4), Cells(5, 4 + 4)).Address & ")"

Notice that I used .Formula (not .formular1c1).

..Address will return the A1 reference style address (with absolute columns and
rows)--like: $D$5:$H$5

There are options that you can use to eliminated the $ if you wanted.

I also had to change the ending cell to avoid a circular reference.

=====
But there are times when using R1C1 reference style (along with the .formular1c1
property) makes life much easier. This looks like it would be one of those
times.



funmi_Bash wrote:

Hello Dave!

I felt constrained to use the R1C1-reference style because the range style
did not give me the expected result, namely:
Range(Cells(5, 4 + 5), Cells(5, 4 + 5)).Formula = "=Sum(Range[Cells(5,
4), Cells(5, 4 + 5)])" does not work.

Had this worked, it would've been so much easier to just substitute the
figures with the appropriate algebraic variables/expressions.

"Dave Peterson" wrote:

First, since you're using the formula in R1C1 reference style, you should be
using .FormulaR1C1.

Second,

Range(Cells(5, 9), Cells(5, 9))

represents a single cell.

You could either use:

cells(5,9).formulaR1C1 = ...
or even
range("I5").formular1c1 = ....



funmi_Bash wrote:

Dave,
I would just like to say a very BIG 'Thank You" for this post.

Here's the working implementation for the benefit of everyone:

Range(Cells(5, 9), Cells(5, 9)).Formula = "=SUM(RC[" & a0 & "]:RC[" & a1
& "])"

Thanks again, Dave!

"Dave Peterson" wrote:

dim s0 as string 'I'd use long's
dim s1 as string

s0 = "3"
s1 = "6"

somecell.formular1c1 = "=SUM(RC[" & s0 & "]:RC[" & s1 & "])"

funmi_Bash wrote:

I need to insert this formula "=SUM(RC[s0$]:RC[s1$])" in a cell such that the
values a0 and a1 are variables.
How can this be done? Please assist and advice.

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson
.