LINEST IN VBA with Non-Contiguous Ranges

**Marston** · 07-26-2011, 08:15 PM

I'm trying to create a range from non-contiguous ranges and utilize Linest function.

I have a worksheet attached.
In this worksheet there is data in cells A2:K92 and again from O2:Q92

The data in Column A matches the data in column O
The data in Column C matches the data in column P
The data in Column K matches the data in column Q

There is also an array function in the worksheet from S2:U6

={linest(Q2:Q92,O2:P92,0,true)}

There is a macro in the worksheet

Sub Macro()
Dim Yrng As Range
Dim Xrng As Range
Dim v
Set Yrng = Range("Q2:Q92")
Set Xrng = Range("P2:P92","O2:O92")
v = Application.WorksheetFunction.LinEst(Yrng,Xrng,0,True)
Range("S8:U12") = v
End Sub

When I run the macro I get the exact same results as the Linest.

What I need to do is create it out of the non-contiguous ranges

But when I try:

Sub Macro()
Dim Yrng As Range
Dim Xrng As Range
Dim v
Set Yrng = Range("K2:K92")
Set Xrng = Range("A2:A92","C2:C92")
v = Application.WorksheetFunction.Linest(Yrng,Xrng,0,true)
Range("S8:U12") = v
End Sub

The values I get back are not the same - even though the inputs are the same (as shown by the data in column M

What gives?

**Marston** · 07-27-2011, 12:47 AM

Let me start by saying I'm very, very rusty VB.

Here's my routine:

Sub Macro()
Dim Yrng As Range
Dim Xrng As Range
Dim v
Dim i As Integer
Set Yrng = Range("K2:K113")
For i = 0 to 9
Set Xrng = Range("A2").Offset(0,i).Resize(1,112)
v = Application.WorksheetFunction.LinEst(Yrng,Xrng,0,True)
Range("M2").Offset(0,i) = v(1,1)
Range("M2").Offset(1,i) = Abs(v(1,1)/ v(2,1))
Range("M2").Offset(2,i) = v(3,1)
Next i
End Sub

When I hit the v = Application.....

I get Run-time error '1004':

Unable to get the LinEst property of WorksheetFunction class

I know when I change Set Xrng to Range("A2:A113") the Linest runs, so I'm guessing my
Set Xrng line is where the trouble is.

Is there a better way to do this?

Thanks in advance

**Marston** · 07-27-2011, 12:49 AM

Cycling through a series of columns ....
Let me start by saying I'm very, very rusty VB.

Here's my routine:

Please Login or Register  to view this content.

When I hit the v = Application.....

I get Run-time error '1004':

Unable to get the LinEst property of WorksheetFunction class

I know when I change Set Xrng to Range("A2:A113") the Linest runs, so I'm guessing my
Set Xrng line is where the trouble is.

Is there a better way to do this?

Thanks in advance

**Mordred** · 07-27-2011, 01:01 AM

Hi Marsten, please wrap your code in code tags as per rule #3 of the forum rules located here. Once you do that, someone will be able to help you.

Regards

**Marston** · 07-27-2011, 01:09 AM

Sorry.....

done.....

**snb** · 07-27-2011, 04:21 AM

1 better way is to use code tags in this forum (see forum rules)

Please Login or Register  to view this content.

**Marston** · 07-27-2011, 11:29 AM

I have a routine that I'm trying to write that creates the regression coefficient, T-stat, and R-square for each coefficient within every combination of 10 different sets of variables.

I have 11 columns of data.

Columns A-J contain each candidate 'X' or independent values for the regression
Column K contains the dependent "Y" variable

As a starting point - just to make sure this is working, I'm only trying to write out the coefficient and T-stat of the first variable and the T-stat of the entire batch of variables used. I will eventually need to have it write out all of the coefficients and T-stats of all of the independent variables.

The idea would be to calc this information for each unique combination of independent variables.

K = function (A,B,C,D,E,F,G,H,I,J)
K = function (A,B,C,D,E,F,G,H,I)
K = function (A,B,C,D,E,F,G,H,J)
K = function (A,B,C,D,E,F,H,I,J)
.
.
.
K = function (J)

where the letters represent each of the columns of data.

The code to select each of the columns appears to work - but when I go to push it into the Linest function, I get a runtime error 1004 - unable to get the Linest property....

Thoughts?

Code listed below and file attached.

Please Login or Register  to view this content.

Thanks in advance...

**Marston** · 07-27-2011, 03:01 PM

Trying to use WorksheetFunction.Linest
When I use a fixed range it works, when I create the range in the code below, it does not.
Variable that appears to have problem sis xRng.
Appreciate any help

Please Login or Register  to view this content.

**shg** · 07-27-2011, 09:07 PM

Try this:

Please Login or Register  to view this content.

**Marston** · 07-28-2011, 01:01 AM

Thanks for the this - I will try in AM.

Sorry for multiple posts. Everytime I hit submit, my browser crashed. I'd go back in a few hours later and nothing showed. So I posted again.

**shg** · 07-28-2011, 10:16 AM

Several threads merged.

LINEST IN VBA with Non-Contiguous Ranges

LinkBack

Thread Tools

Rate This Thread

Display

LINEST IN VBA with Non-Contiguous Ranges

Cycling through a series of columns ....

Cycling through a series of column data

Re: Cycling through a series of column data

Re: Cycling through a series of column data

Re: LINEST IN VBA with Non-Contiguous Ranges

Runtime Error 1004 on Linest

Runtime Error 1004 when using WorksheetFunction.Linest

Re: Runtime Error 1004 on Linest

Re: Runtime Error 1004 on Linest

Re: LINEST IN VBA with Non-Contiguous Ranges

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions