How to plug a range object into a formula

Question

I want to plug a range object into a formula. An example should look roughly like this:

Dim x As Range
Set x = Range(Cells(1, 1), Cells(2, 1))

Range("C1").Formula = "=SUM(" & x & ")"

The result should be "=SUM(A1:A2)" in cell C1.

The point is to plug the the range object into a formula. I used SUM as an example, the real formula is more complicated.

I guess there is an easy answer to this, e.g. some method for a range object, but I have't found it after some pondering ...

Thanks in advance, Dainis

Answer 1

Use x.address, that should do the trick

Answer 2

You need

Range("C1").Formula = "=SUM(" & x.Address(False, False) & ")"

See also

http://www.dailydoseofexcel.com/archives/2004/04/16/worksheet-formulas-in-vba-part-i/

http://www.dailydoseofexcel.com/archives/2004/04/16/worksheet-formula-in-vba-part-ii/