Inserting formulas depending on a variable

[Alexander]

It is possible to insert a formula in the cell by Formula property, for
instance:
Range("A1") Formula = "=Sum(A2:A12)"
How can I to change cells which are used in formula, depending on a
variable. For instance, I need to change the range A2:A12 to Ax:Ay depending
on variable - rng=Range("Ax:Ay") ?
I am using Excel2003.

 

[Don Guillett]

One way is to put this into a REGULAR module in your workbook.
Then in cell a1 =mysum(2,12)

Function mysum(x, y)
mysum = Application.Sum(Range(Cells(x, "a"), Cells(y, "a")))
End Function

 

[Gary''s Student]

Use the Address of the range you want in the function:

Sub rangedemo()
Dim r As Range
Set r = Range("A24")
Range("A1").Formula = "=SUM(" & r.Address & ")"
End Sub