Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' application or object defined error | Excel Programming | |||
runtime error '1004' application or object defined error. Please help | Excel Programming | |||
Runtime error 1004 | Excel Programming | |||
runtime error 1004 | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |