Thursday, December 12, 2019

VBA Macro to run linear regression between two variables,VBA Teacher Sourav,Kolkata 08910141720

I have observations for two variable ,the column speed is for X variable,The column distance is for Y variable



Now I have Data Analysis toolpack installed,so this macro will calculate linear regression for X and Y

Sub automatelinearregression()
Sheets("Sheet1").Select

lrA = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (lrA)

lrC = Cells(Rows.Count, "B").End(xlUp).Row
MsgBox (lrC)
lr = Application.Max(lrA, lrC)  'or Min? I expect the x and y have to have the same number of values?
MsgBox (lr)
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("B1:B" & lr), ActiveSheet.Range("A1:A" & lr), False, True, 95, ActiveSheet.Range("$O$5"), False, False, False, False, , False
End Sub

Result:



Source:http://www.vbaexpress.com/forum/showthread.php?55218-VBA-to-run-a-Linear-Regression-Automatically

No comments:

Post a Comment