M

#### marston.gould

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", "D2112", "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