I'm trying to retrieve the r2 from LINESt results via VBA--I don't want to
create a chart and pull it from the chart's trendline. I can retrieve m and b
with no problem, but when I try the following I get an error.

r2 = Application.Evaluate("=index(linest(" & Selection.AddressLocal() &
",,,TRUE),3)")

Any and all help greatly appreciated.
Thanks,
j

Here is a working example, with data in A2:E12 (headers in row 1):

Sub TryNow()
Dim myArr As Variant
Dim i As Integer
Dim j As Integer

myArr = Application.WorksheetFunction.LinEst(Range("E2:E12 "), _
Range("A2:D12"), True, True)

MsgBox "R2 stat is " & myArr(3, 1)

If MsgBox("Want other values?", vbYesNo) = vbYes Then
For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
MsgBox "Row " & i & ", Column " & j & " of the returned array is " _
& IIf(IsError(myArr(i, j)), "an error", myArr(i, j))
Next j
Next i
End If
End Sub

HTH,
Bernie
MS Excel MVP


"jstansbury" (nonotspam) wrote in message
...
I'm trying to retrieve the r2 from LINESt results via VBA--I don't want to
create a chart and pull it from the chart's trendline. I can retrieve m and b
with no problem, but when I try the following I get an error.

r2 = Application.Evaluate("=index(linest(" & Selection.AddressLocal() &
",,,TRUE),3)")

Any and all help greatly appreciated.
Thanks,
j

Bernie,
Works like a charm. Thank you very much!
Much appreciated,
j


"Bernie Deitrick" wrote:

Here is a working example, with data in A2:E12 (headers in row 1):

Sub TryNow()
Dim myArr As Variant
Dim i As Integer
Dim j As Integer

myArr = Application.WorksheetFunction.LinEst(Range("E2:E12 "), _
Range("A2:D12"), True, True)

MsgBox "R2 stat is " & myArr(3, 1)

If MsgBox("Want other values?", vbYesNo) = vbYes Then
For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
MsgBox "Row " & i & ", Column " & j & " of the returned array is " _
& IIf(IsError(myArr(i, j)), "an error", myArr(i, j))
Next j
Next i
End If
End Sub

HTH,
Bernie
MS Excel MVP


"jstansbury" (nonotspam) wrote in message
...
I'm trying to retrieve the r2 from LINESt results via VBA--I don't want to
create a chart and pull it from the chart's trendline. I can retrieve m and b
with no problem, but when I try the following I get an error.

r2 = Application.Evaluate("=index(linest(" & Selection.AddressLocal() &
",,,TRUE),3)")

Any and all help greatly appreciated.
Thanks,
j