469,081 Members | 1,436 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

Linear regression and selecting a range of contiguous cells in a column

1
Hi, I'm a complete novice when it comes to VB so need some help.

I would like a macro that performs linear regression for different cell ranges each time it is run, depending on the number of contiguous cells in the columns.

I have managed to recorded a macro in Excel to perform a linear regression using the Data Analysis Toolpak for a fixed cell range:

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 15/09/2008
'

'
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$B$1:$B$3"), _
ActiveSheet.Range("$A$1:$A$3"), False, False, , ActiveSheet.Range("$A$9") _
, False, False, False, False, , False
End Sub


And i have found some code that selects an entire range of contiguous cells in a column:

Sub b()
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select
End Sub


So I thought that if i replaced ActiveSheet.Range("$B$1:$B$3") in the above with this code that hopefully it would work:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 15/09/2008
'

'
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown)).Select, _
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select, False, False, , ActiveSheet.Range("$A$9") _
, False, False, False, False, , False
End Sub


... but unfortunately not and I get the excel error message saying: "Regression - Input Y range must be a contiguous reference", so I obviously need another approach!

I'd be very grateful if anyone can help point me in the right direction.

Thanks!
Sep 15 '08 #1
0 3137

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

15 posts views Thread by nikie | last post: by
3 posts views Thread by sql guy123 | last post: by
1 post views Thread by wirecom | last post: by
1 post views Thread by Nod Lee | last post: by
3 posts views Thread by ntuyen01 | last post: by
1 post views Thread by Bob | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.