populating cell addresses in formulas

[lharris]

I need to return a range of cell addresses in a formula but it won't let me append to the formula. If I type activecell.formula = "=IRR(" even that will give me an application or object defined run time error.

Help! I need to be able to populate a cell address range to a formula.

 

[Andrew Poulsom]

Like this:

ActiveCell.Formula = "=IRR(A1:A5)"

or if you have a Range object variable, like this:

Dim Rng As Range
Set Rng = Range("A1:A5")
ActiveCell.Formula = "=IRR(" & Rng.Address(False, False) & ")"

 

[lharris]

On 2002-09-11 08:55, Andrew Poulsom wrote:
Like this:

ActiveCell.Formula = "=IRR(A1:A5)"

or if you have a Range object variable, like this:

Dim Rng As Range
Set Rng = Range("A1:A5")
ActiveCell.Formula = "=IRR(" & Rng.Address(False, False) & ")"

Wonderful! I'm so close, thanks:

Here's the code I'm using for the variant and it doesn't give me errors but it doesn't pull what I want it to (a1 through the end of the populated cells) It pulls 20 cells down from that address for some reason. Any ideas?

Dim Reference As Range
Set Reference = ActiveCell.Range("a1", ActiveSheet.Range("a65536").End(xlUp))
ActiveCell.Formula = "=IRR(" & Reference.Address(False, False) & ")"

 

[Andrew Poulsom]

Try:

Set Reference = Range("A1:A" & Range("A65536").End(xlUp).Row)

This selects all the used cells in column A.

Not sure why you are using ActiveCell. If the active cell is B5 then:

ActiveCell.Range("A1").Select

selects cell B5. In other words the range is relative to the active cell.

 

[lharris]

Perfect! Thanks so much!!