CODEWITHSUNDEEP
vbaexcel
jules325
jules325

Reputation: 179

Pass a range as an argument in a formula using VBA

I'm really REALLY new using VBA, so I apologize for this question in advance:

Basically, I'm trying to write a sub that will calculate IRR on a range of numbers that is dynamic. So I tried to create a variable which would select the range of numbers, and then using a named range for the output cell(IRR), have the result of the formula show. Below is the code I have:

Sub irrCalc()

Dim calcrange As Range


Set calcrange = Range("B55").End(xlToRight).Select


Range("IRR").Formula = "IRR(calcrange)"


End Sub

The error I'm getting is 'Object required', and its pointing to the center line of code "Set calcrange".

I have been looking for an answer but I can't seem to find (or understand) what the problem is...Again I know this is probably ridiculously easy but I'm a bit stumped. Thanks in advance

Upvotes: 2

Views: 2248

Answers (2)

John Muggins
John Muggins

Reputation: 1198

Range("IRR").Formula = "IRR(calcrange)"

-You are putting a formula into the cell requesting it to run a named range through a subroutine called "IRR".

Setting the range in a module does not make it a named range. You need the following code to do that:

Set rng1 = Range("B55:B200") ' How many columns need to be calculated
ActiveWorkbook.Names.Add Name:="calcrange", RefersTo:=Rng1

Now you have a named range to work with.

Do you have a subroutine "IRR" to work with? It looks like you are referring to a single cell named "IRR." It's confusing and probably erroneous to use the same name for different things like ranges and functions or subroutines. Can you post your subroutine "IRR?"

Upvotes: 0

Ralph
Ralph

Reputation: 9444

I can't be sure. But maybe you're looking for something like this:

Sub irrCalc()

Dim calcrange As Range

Set calcrange = Range(Range("B55"), Range("B55").End(xlToRight))

Range("IRR").Formula = "=IRR(" & calcrange.Address & ")"

End Sub

Upvotes: 3

Related Questions