PDA

View Full Version : Solved: Changing Range for Formula



lukecj
11-18-2010, 03:35 PM
First of all, I love everyone who offers their knowledge on this site. To newbies in VBA, you are like freakin' Jedi...seriously. I have had this issue for some time and I'm finally coming to grips that I can't solve it myself. Essentially, I want to insert a formula using the standard code:

Active.Cell.FormulaR1C1 = ""

However, I want to insert it at the bottom of a range that will change based upon the date range we are looking at. So, it can't just be a fixed range like the last two lines of code here:



Sheets("Sheet2").Select
Range("A2").Select
Selection.End(xlDown).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "Compounded"
Selection.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "Annualized"
Selection.Offset(-1, 1).Select
ActiveCell.FormulaR1C1 = "=PRODUCT(1+R[-71]C:R[-2]C)-1"
Selection.FormulaArray = "=PRODUCT(1+R[-71]C:R[-2]C)-1"



The idea is that is will work like autosum, automatically selecting the range directly above the cell where the formula is entered. Basically, I need the formula to be flexible so if the range is B3:B100 instead of the current range it will use the entire range instead of what I specified in the code. Spreadsheet attached.

Please let me know if I need to clarify. Thanks.

Charles

Bob Phillips
11-18-2010, 03:55 PM
I don't get the formula, but ...



With Worksheets("Sheet2")

With Range("A2").End(xlDown).Offset(2, 0)

.Value = "Compounded"
.Offset(1, 0).Value = "Annualized"
.Offset(0, 1).FormulaArray = "=PRODUCT(1+R3C:R[-2]C)-1"
End With
End With

lukecj
11-18-2010, 04:00 PM
The formula gives you the cumulative return of the monthly returns for the respective date range. Thanks for your reply. I'll let you know if I run into issues.

lukecj
11-18-2010, 04:08 PM
Works beautifully, sometimes I fell really stupid. What an easy fix...Thanks...

Bob Phillips
11-18-2010, 04:32 PM
If it is a cumulative return, why not use SUM?

lukecj
11-18-2010, 08:08 PM
Returns compound on top of each other. Therefore, any series of returns must be expressed geometrically (i.e multiplied). For instance, let's say you have $100 dollars and you can invest it at 10% per annum. After the end of 1 year, you will have $110. If you earn another 10% the following year, you earn 10% on your original $100 dollar investment and 10% on the $10 dollars you earned the year before. So, your return is not 20%; it is 21%. This is expressed geometrically with the following formula:

100*1.10^2 = 121

lukecj
11-18-2010, 09:03 PM
Hey - you answer a ton of questions on this forum. Out of curiosity, do you have any suggestions to become more proficient at VBA? Thanks.

Bob Phillips
11-19-2010, 04:36 AM
Returns compound on top of each other. Therefore, any series of returns must be expressed geometrically (i.e multiplied). For instance, let's say you have $100 dollars and you can invest it at 10% per annum. After the end of 1 year, you will have $110. If you earn another 10% the following year, you earn 10% on your original $100 dollar investment and 10% on the $10 dollars you earned the year before. So, your return is not 20%; it is 21%. This is expressed geometrically with the following formula:

100*1.10^2 = 121

I'm out of my comfort zone here, but aren't there financial functions that can work this out?

Bob Phillips
11-19-2010, 04:39 AM
Hey - you answer a ton of questions on this forum. Out of curiosity, do you have any suggestions to become more proficient at VBA? Thanks.
It is not easy, you need to have a practical goal.

My only advice is along the lines of
- buy a good book, something like Walkenbach's VBA For Dummies
- get yourself a project that it meaningful to you and will allow you to practice the book's techniques
- read the posts here, read the answers and if you don't get what is being done jump in and ask (it is a positive contribution, believe me, there will probably be others that wanted to ask but don't).

lukecj
11-19-2010, 11:14 AM
Actually, yes. There is a function called geomean, but you can only use it if all of your returns are positive. It's a little limiting. Thanks for the advice.