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