Runtime Error on Linest
wrote:
The problem I'm having is when I test fixed range
declarations in the independent variables - the LinEst
function works. When I create a range based on dynamic
ranges - it doesn't. Could use some help.
The issue is not fixed v. dynamic range. The issue is range v. range
collection.
Consider the first iteration.... The string r becomes
"A2:A112,B2:B112,C2:C112,D2:D112,E2:E112,F2:F112,G 2:G112,H2:H112,I2:I112,J2:J112",
so xRng becomes the collection of those ranges.
Apparently, WorksheetFunction.Linest does not work with range collections.
Not surprising: neither does the Excel LINEST function. Instead, as you
noted, xRng must be Range("A2:J112").
Ostensibly, we might write:
Set xRng = Range(rngs(idx(1)), rngs(idx(nSelect)))
Note that we do not need to construct the string r at all.
However, that will not work for you in general because Linest assumes that
you are referring to __all__ interstitial columns.
For example, in iteration 977, r becomes "A2:A112,J2:J112". You intend to
Linest to consider only a 2-column multiple regression using only A2:A112
and J2:112. But xRng will be set to Range("A2:J112"), which Linest will
interpret as a 10-column multiple regression.
I believe the only fix is to copy the selected columns of each combination
to a temporary contiguous range, and pass that temporary range to Linest.
The following demonstrates the intended change. Note that it is untested.
If you are unable to debug any hopefully-minor mistakes, let me know, and I
can spend more time with it.
Note: There is no benefit to using type Integer instead of type Long in
this context.
PS: When you get done with development, it would be prudent to bracket the
body of the macro with the following statements in order to improve
performance:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
[... rest of macro ...]
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
-----
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
|