Inserting formulas depending on a variable

  • Thread starter Thread starter Alexander
  • Start date Start date
A

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.
 
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
 
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
 
Back
Top