Formula/Text?

  • Thread starter Thread starter David Fixemer
  • Start date Start date
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
 
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
 
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)
 
Tom,

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

David
 
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.
 
correction

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

edited out the last ampersand.
 
Back
Top