Formula/Text?
 

I've seen several past postings that imply formulas should
be entered as text ("=sum(A" + trim(str(firstrow))
+":A"+trim(str(lastrow))+")"

that activecell.text = . . .
is the same as activecell.formulaR1C1 = . . .

However, the following lines of code makes me believe this
is not exactly correct?

ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.FormulaR1C1 = "=SUM(R1C:R3C)"

How would I use variables instead of numbers and/or
offsets?

x=1
y=3

ActiveCell.FromulaR1C1 = "=SUM(RxC:RyC)"

Thanks for any/all assistance,

David Fixemer

Formula/Text?
 

you can't assign anything to the text property of a range - it is read only

ActiveCell.FormulaR1C1 = "=SUM(R[" & x & "]C:R[" & y & "]C)"

rather than use Trim(str(number)) to get rid of the space put in by str, use
cstr(number) or just number and let vba do the conversion.
lngNum = 200
"number " & lngNum & " greater than 3"

Formula expects A1 style addressing
FormulaR1C1 expects R1C1 style addressing

--
Regards,
Tom Ogilvy


"David Fixemer" wrote in message
...
I've seen several past postings that imply formulas should
be entered as text ("=sum(A" + trim(str(firstrow))
+":A"+trim(str(lastrow))+")"

that activecell.text = . . .
is the same as activecell.formulaR1C1 = . . .

However, the following lines of code makes me believe this
is not exactly correct?

ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.FormulaR1C1 = "=SUM(R1C:R3C)"

How would I use variables instead of numbers and/or
offsets?

x=1
y=3

ActiveCell.FromulaR1C1 = "=SUM(RxC:RyC)"

Thanks for any/all assistance,

David Fixemer

Formula/Text?
 

David,

Uh?

ActiveCell.FormulaR1C1 = "=SUM(R[-" & y & "]C:R[-" & y & "]C)"
ActiveCell.FormulaR1C1 = "=SUM(R" & x & "C:R" & y & "C)"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Fixemer" wrote in message
...
I've seen several past postings that imply formulas should
be entered as text ("=sum(A" + trim(str(firstrow))
+":A"+trim(str(lastrow))+")"

that activecell.text = . . .
is the same as activecell.formulaR1C1 = . . .

However, the following lines of code makes me believe this
is not exactly correct?

ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.FormulaR1C1 = "=SUM(R1C:R3C)"

How would I use variables instead of numbers and/or
offsets?

x=1
y=3

ActiveCell.FromulaR1C1 = "=SUM(RxC:RyC)"

Thanks for any/all assistance,

David Fixemer

Formula/Text?
 

Tom,

Thanks for the info. How would the aforementioned
formula look in "A1 notation"?

David

Formula/Text?
 

ActiveCell.Formula = "=SUM(A" & x & ":A" & y ")"

Which is absolute with respect to the location of the cell where you will
place the formula. If you want relative to that cell, I think you would
need to calculate the values for x and y so you can do it absolute - in
other words, excel won't do the adjustment for you.

--
Regards,
Tom Ogilvy


"David Fixemer" wrote in message
...
Tom,

Thanks for the info. How would the aforementioned
formula look in "A1 notation"?

David

Formula/Text?
 

correction

ActiveCell.Formula = "=SUM(A" & x & ":A" & y & ")"

edited out the last ampersand.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
ActiveCell.Formula = "=SUM(A" & x & ":A" & y ")"

Which is absolute with respect to the location of the cell where you will
place the formula. If you want relative to that cell, I think you would
need to calculate the values for x and y so you can do it absolute - in
other words, excel won't do the adjustment for you.

--
Regards,
Tom Ogilvy


"David Fixemer" wrote in message
...
Tom,

Thanks for the info. How would the aforementioned
formula look in "A1 notation"?

David