Inserting formulas depending on a variable
 

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.

Inserting formulas depending on a variable
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Alexander" wrote in message
...
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.

Inserting formulas depending on a variable
 

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

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

--
Gary''s Student - gsnu200825


"Alexander" wrote:

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.