By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

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

P: 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
Share this question for a faster answer!
Share on Google+

Post your reply

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