How can I use this expression "=SUM(RC[a0]:RC[a1])" work in Excel

[funmi_Bash]

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]

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

s0 = "3"
s1 = "6"

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

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.

 

[funmi_Bash]

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!

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

s0 = "3"
s1 = "6"

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

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

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!

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

s0 = "3"
s1 = "6"

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

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.

 

[funmi_Bash]

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.

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

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!

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]

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.

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.

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

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!

:

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.

 

[funmi_Bash]

Thanks again Dave!

What can I say? Beauty, eh?

God bless you my man! Real Good!!

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.

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.

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!

:

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.