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

  • Thread starter Thread starter funmi_Bash
  • Start date Start date
F

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.
 
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 said:
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.
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Dave!

What can I say? Beauty, eh?

God bless you my man! Real Good!!

Dave Peterson said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top