473,226 Members | 1,411 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 3475

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

Similar topics

2
by: deko | last post by:
I use a complied query to export to Excel like this: SELECT * INTO . FROM tblExcelData; But I have a situation where I need to export several tables into the same worksheet. The idea is to...
15
by: nikie | last post by:
I'm a little bit stuck with NumPy here, and neither the docs nor trial&error seems to lead me anywhere: I've got a set of data points (x/y-coordinates) and want to fit a straight line through...
3
by: sql guy123 | last post by:
This is a real challenge. I hope someone is smart enough to know how to do this. I have a table TABLE1
1
by: wirecom | last post by:
Hi all, I am seeking a module that will do the equivalent of linear regression in 3D to yield a best fit a plane through a set of points (X1, Y1, Z1), (X1, Y1, Z1),... (Xn, Yn, Zn). The...
1
by: Nod Lee | last post by:
Hi all Is there any tools/objects from Microsoft that I can use to perform linear regression in webform and generate the results into a graph? If not, is there any recommendations for 3rd...
3
by: ntuyen01 | last post by:
Hi All, Does anyone has examples for Linear Regression formula using c#. Or any good third party software create this. Thanks in advance Regards, Ted
1
by: Bob | last post by:
Hi, Does anyone know how to iterate through the individual cells in an excel Range? I believe I have a non-contiguous range from a function call so I don't know the rows and column indices and...
1
by: Socko | last post by:
I'm trying to fix an sub routine in an VB module that basically reads in a MS database and writes it to an Excel Spread sheet. It works just fine except that the data isn't sorted correctly. I have...
7
by: PlsHelp | last post by:
Hi every1,this is my 1st time using this forum....pls can any1 tell me how to create a code in VB that can do LINEAR REGRESSION in excel.....i'm not sure how to xplain exactly what i need but...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.