Solved: Changing Range for Formula

[lukecj]

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:

[VBA]Active.Cell.FormulaR1C1 = ""[/VBA]

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:

[VBA]

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"

[/VBA]

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]

I don't get the formula, but ...

[vba]

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
[/vba]

[lukecj]

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]

Works beautifully, sometimes I fell really stupid. What an easy fix...Thanks...

[Bob Phillips]

If it is a cumulative return, why not use SUM?

[lukecj]

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]

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]

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]

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]

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.