Help me construct a formula in an Excel macro

C

Candace

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?
 
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
 
Quote from DarthSidious:

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

Thank you DarthSidious! My way works but yours looks prettier. I'll try it your way.
 
You must log in or register to reply here.
Back
Top