Runtime Error 1004 on Linest

  • Thread starter Thread starter marston.gould
  • Start date Start date
M

marston.gould

I'm trying to create a VBA macro that calculates the coefficients/T-stats and R-square for each variable with in a regression. The trick is that I have 10 candidate independent variables and I'd like to be able to test each combination of these variables.

I created some code that used fixed ranges for the Linest calc and that worked.
I got some help creating code that selected each unique combination.
But when I put these together - I get a runtime error 1004 on the linest.
I know that it has something to do with how I'm assigning the unique combination to a range that will be used in the Linest calculation.

I would appreciate any thoughts on how to correct this.

Regards - Marston

Here is the code below:

Option Base 1
Option Explicit
Sub combinKofN()
Dim rngs
Dim nRngs As Long, maxCombin As Long, nCombin As Long
Dim nSelect As Long, i As Long, j As Long
Dim r As String
Dim xRng As Range
Dim yRng As Range
Dim v
Dim k As Integer

rngs = Array("A2:A112", "B2:B112", "C2:C112", "D2:D112", "E2:E112", "F2:F112", "G2:G112", "H2:H112", "I2:I112", "J2:J112")
Set yRng = Range("K2:K112")
yRng.Select
nRngs = UBound(rngs)
k = 0
For nSelect = nRngs To 1 Step -1
maxCombin = WorksheetFunction.Combin(nRngs, nSelect)
ReDim idx(1 To nSelect) As Long
For i = 1 To nSelect: idx(i) = i: Next

nCombin = 0
Do
' generate next combination
nCombin = nCombin + 1
r = rngs(idx(1))
For i = 2 To nSelect
r = r & "," & rngs(idx(i))
Next
Set xRng = Range(r)
'visually make sure the correct range is being selected
'xRng = Range("A2:J112") ' this works when I utilize this instead
xRng.Select
v = Application.WorksheetFunction.LinEst(yRng, xRng, 0, True)
' ...coefficient
Range("M2").Offset(4 * k + 2, 0) = v(1, 1)
' ...T-stat
Range("M2").Offset(4 * k + 3, 0) = Abs(v(1, 1) / v(2, 1))
' ...R-squared
Range("M2").Offset(4 * k + 4, 0) = v(3, 1)
k = k + 1

If nCombin = maxCombin Then Exit Do

' next combination index
i = nSelect: j = 0
While idx(i) = nRngs - j
i = i - 1: j = j + 1
Wend
idx(i) = idx(i) + 1
For j = i + 1 To nSelect
idx(j) = idx(j - 1) + 1
Next
Loop
Next
 
Marston
Do you realize you have posted the same message 4 times.
Do you not see them?
They are all there, just wait for someone to answer.
Cimjet
 
See the response in m.p.e.newusers.

And please do not multi-post, especially across newsgroups.

You appear to be using Google Groups. Sometimes it is slow in displaying
new posts.
 
Cimjet said:
Marston
Do you realize you have posted the same message 4 times.
Do you not see them?
Click to expand...

FYI, no, he doesn't.

It appears that Google Groups is having its problems again. None of
Marston's follow-up postings to this thread appear in GG, nor do any his
"duplicate" postings about the same problem in m.p.e.programming.

It is such problems that cause me recently to abandon GG. Instead, I use
the newserver news.eternal-september.org, which you can register to use for
free at http://eternal-september.org. Then I set up Outlook Express as a
newsreader on my computer.

news.eternal-september.org has been very reliable for the short time that I
have been using it, about one month.

(Of course, Marston will not see this until the GG problem is cleared up.)
 
Hi Joe
I also use Eternal-September, never had problem with it.
I've been using it since Microsoft closed there NG server.
It looks like GG is following in MS footsteps.
Best regards
Cimjet
 
Hi -

I tried the suggested changes -

Still having a few difficulties.
First off I notice when I use

Dim v As Long - I get a compile error. I think this is because v is an array not a long. When I leave blank or use Dim v as Variant it appears to compile.

In either of the situations above, when I run with the suggested changes - I still get the Runtime Error.

When I watch what is happening in debug - a couple of things I notice.
First off - the first pass through - I see the xRng(0,i-1) = Range(rngs(idx(i)) being loaded with each of the column ranges.

However - when I throw in a xRng.Select before the v = Application.....
it only selects the rows in column X. Additionally, it does not appear thatthe values from columns A-J get loaded into X-AH as I think is the intention.

I tried to add

Range("X2").Resize(nRows).Offset(0,i-1) = Range(rngs(idx(i))) in the For/Next loop that is For n = 1 to nSelect just before v calculation - but that doesn't seem to set the values either. I also tried a couple of other manipulations of loading the range into an array and then moving that array to a different set of columns - but that's not working.

I also think - should the xRng be set to something like

Range("X2").Resize(nRows, nSelect) ?

Thanks - advance -

And only posting once!
 
I tried the suggested changes - [....]
Dim v As Long - I get a compile error.
Click to expand...

I presume that your referring to a suggested solution that I posting
m.p.e.newusers, which I also sent to you in email. For the benefit of
others, I will include a copy below.

Note that I did not suggest Dim v As Long. Yes, that should remain Dim v
(As Variant).

I did suggest Dim k As Long instead of As Integer.

In either of the situations above, when I run with
the suggested changes - I still get the Runtime Error.
Click to expand...

Since it appears that you did not simply copy-and-paste my suggestion, there
might be other changes that you made and I am not aware of. Alternatively,
I might have made some mistakes; as I mentioned previously, I could not test
the suggest except superficially. Alternatively, your use of Linest might
be failing for other reasons.

In either case, if you need my help debugging the algorithm, I will need to
see the macro as you have it now, and I will need some sample data.

The best way to accomplish that is for you to send me an example Excel file
in email. Or you can upload the Excel file to a file-sharing website. The
following is a list of some free file-sharing websites. I use
box.net/files.

MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files

However - when I throw in a xRng.Select before the
v = Application.....
it only selects the rows in column X.
Click to expand...

Yes, I would expect that. Why would you do xRng.Select? Just for debugging
purposes?

Since my Linest parameter is xRng.Resize(nRows, nSelect), I would write
xRng.Resize(nRows, nSelect).Select. Alternatively, perhaps it suit your
purposes to simply write:

MsgBox xRng.Resize(nRows, nSelect).Address

Range("X2").Resize(nRows).Offset(0,i-1) = Range(rngs(idx(i)))
in the For/Next loop that is For n = 1 to nSelect just
before v calculation
Click to expand...

My for-loop already had xRng.Offset(0, i - 1).Resize(nRows, 1) =
Range(rngs(idx(i))), essentially the same thing.

Since you say you needed to "throw in" something, I suspect you are no long
working with exactly the code that I suggested.

I also think - should the xRng be set to something like
Range("X2").Resize(nRows, nSelect) ?
Click to expand...

I don't know where you are talking about putting that. So I cannot comment.

-----

My original suggestions....

Newsgroups: microsoft.public.excel.newusers
Subject: Re: Runtime Error on Linest
Date: Wed, 27 Jul 2011 13:23:48 -0700

Option Base 1
Option Explicit

Sub combinKofN()
Dim rngs
Dim nRngs As Long, maxCombin As Long, nCombin As Long
Dim nSelect As Long, i As Long, j As Long
'Dim r As String '***remove***
Dim xRng As Range
Dim yRng As Range
Dim v
Dim k As Long '***changed***
Dim nRows As Long '***new***


Set xRng = Range("X2") '***new***
Set yRng = Range("K2:K112")
yRng.Select
nRows = yRng.Count '***new***

rngs = Array("A2:A112", "B2:B112", "C2:C112", "D2:D112", _
"E2:E112", "F2:F112", "G2:G112", "H2:H112", _
"I2:I112", "J2:J112")
nRngs = UBound(rngs)

k = 0
For nSelect = nRngs To 1 Step -1
maxCombin = WorksheetFunction.Combin(nRngs, nSelect)
ReDim idx(1 To nSelect) As Long
For i = 1 To nSelect: idx(i) = i: Next

nCombin = 0
Do
' generate next combination
nCombin = nCombin + 1
For i = 1 To nSelect '***changed***
xRng.Offset(0, i - 1).Resize(nRows, 1) = _
Range(rngs(idx(i)))
Next
v = Application.WorksheetFunction.LinEst(yRng, _
xRng.Resize(nRows, nSelect), 0, True)
' ...coefficient
Range("M2").Offset(4 * k + 2, 0) = v(1, 1)
' ...T-stat
Range("M2").Offset(4 * k + 3, 0) = Abs(v(1, 1) / v(2, 1))
' ...R-squared
Range("M2").Offset(4 * k + 4, 0) = v(3, 1)
k = k + 1

If nCombin = maxCombin Then Exit Do

' next combination index
i = nSelect: j = 0
While idx(i) = nRngs - j
i = i - 1: j = j + 1
Wend
idx(i) = idx(i) + 1
For j = i + 1 To nSelect
idx(j) = idx(j - 1) + 1
Next
Loop
Next

xRng.Resize(nRows, nRngs).Clear '***new***

End Sub

-----

Newsgroups: microsoft.public.excel.newusers
Subject: Re: Runtime Error on Linest
Date: Wed, 27 Jul 2011 13:48:22 -0700

Small improvements....
Set xRng = Range("X2") '***new***
Click to expand...

Change to

Set xRng = Range("X2:X112")

Or more generally, after determining nRows:

Set xRng = Range("X2").resize(nRows)
xRng.Offset(0, i - 1).Resize(nRows, 1) = _
Range(rngs(idx(i)))
Click to expand...

With the change above:

xRng.Offset(0, i - 1) = Range(rngs(idx(i)))
 
In either of the situations above, when I run with
the suggested changes - I still get the Runtime Error.
Click to expand...

Problem solved. Marston sent me an example Excel file in email. With that,
I discovered a problem in my original coding, to wit....

xRng.Offset(0, i - 1).Resize(nRows, 1) = _
Range(rngs(idx(i)))

should be

xRng.Offset(0, i - 1).Resize(nRows, 1) = _
Range(rngs(idx(i))).Value
 
Back
Top