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
> .
>
|
Similar Threads
