How to print LinEst result to a spreadsheet using VBA

[Fei]

Hi all,

I have written vbs codes to run a linear regression – 5 independent
variables. I want to get regression result print in the spreadsheet
where the data is and the statistics for regression are the same as
result from analysis tool package…

I know Regression= Application.LinEst(y, x, True, True) should be use,
but no idea how to get regression output to a spreadsheet.Could
someone please provide some solutions?

Thanks a ton

Fei

 

[Dave Peterson]

How about just plopping the =linest() formula directly into the range on the
worksheet.

You could record a macro when you do it manually:

dim DestCell as range
set destcell = worksheets("Sheet999").range("x99")

destcell.resize(5,2).formulaarray = "=linest(....)"

 

[Fei]

Thanks Dave.

VBA code is preferred, as I run regressions for different worksheets...

 

[Dave Peterson]

I don't understand. You can use VBA code to populate the cells with a formula.
You could even convert the results of the formula to values.

But if you want:

Option Explicit
Sub Testme()

Dim Xs As Variant
Dim Ys As Variant
Dim res As Variant

Dim HowManyRows As Long
Dim HowManyCols As Long
Dim DestCell As Range

With ActiveSheet
'test data and the topleftcell of the result range.
Xs = .Range("b1:b10").Value
Ys = .Range("a1:a10").Value
Set DestCell = .Range("f9")
End With

res = Application.LinEst(Xs, Ys, , True)

HowManyRows = UBound(res, 1) - LBound(res, 1) + 1
HowManyCols = UBound(res, 2) - LBound(res, 2) + 1

DestCell.Resize(HowManyRows, HowManyCols).Value = res

End Sub