Inserting formulas depending on a variable

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.
 
D

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
 
G

Gary''s Student

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top