Help me construct a formula in an Excel macro

I want to use a macro to place a formula into my spreadsheet, the range of which I want to define based upon the contents of a cell. As example, having entered a 6 into cell A11 on my Inputs worksheet, I want my formula to use six rows of data from my Data worksheet, like this: =LINEST(Data!E2:E7,Data!F2:F7). I later copy the formula so I want to keep the cell references relative.

With the help of Record Macro, I found out I have to use
ActiveCell.FormulaR1C1 = "=LINEST(Data!RC[3]:R[5]C[3],Data!RC[4]:R[5]C[4])". Works great if I don't mind editing the macro whenever I want a bigger or smaller range. After throwing all kinds of code at the problem (STR function, RIGHT function, even ConvertFormula) I still can’t seem to get it right.



Anyone out there that can help?

 

No worries, I got it.

 

Code:

Sub Macro1()
' Keyboard Shortcut: Ctrl+Shift+E
    ActiveCell.Formula = "=LINEST(Data!E2:E" & (2 + Range("A11").Value - 1) & ",Data!F2:F" & (2 + Range("A11").Value - 1) & ")"
End Sub

Select your cell and hit Ctrl+Shift+E. It will create the formula based on value in A11

 

Thank you DarthSidious! My way works but yours looks prettier. I'll try it your way.