Formula/Text?

D

David Fixemer

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
 
T

Tom Ogilvy

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
 
B

Bob Phillips

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)
 
D

David Fixemer

Tom,

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

David
 
T

Tom Ogilvy

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

Tom Ogilvy

correction

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

edited out the last ampersand.
 
Top