Expert: Jan Karel Pieterse - 7/9/2009

Question
Hi

I am trying to calculate polynomial coefficients in excel. I can write
ActiveCell.FormulaR1C1 =   "=INDEX(LINEST(R2C4:R1005C4,R2C6:R1005C6^{1,2,3,4,5,6}),1)"
but I want to specify the range rather than having to say "R2C4:R1005C4". How can I do it? One way I can think is to make the string programatically but that doesnt sound like good.

Thanks in advance,
Kaushalya

Answer
Hi Kaushalya,

You could do something like this:

Sub Example()
   Dim oRange As Range
   On Error Resume Next
   Set oRange = Application.InputBox("Please select cells", "Select cells for formula", , , , , , 8)
   If Not oRange Is Nothing Then
       ActiveCell.Formula = "=INDEX(LINEST(" & oRange.Address & ",F2:F1000^{1,2,3,4,5,6}),1)"
   End If
End Sub