Help me construct a formula in an Excel macro

Discussion in 'Automated Trading' started by Candace, Jan 28, 2010.

  1. 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?
  2. Candace


    No worries, I got it.
  3. 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
  4. Candace


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