Regarding question 1 in post #5, no, 16 is the Regress limit. If you want to do more, you will have to pursue other means. e.g. SAS, R, etc. You can view those other ways by searching for "excel regress 16".
For question 2, after removing the data columns of more than 16 in that one listobject, you can try this code in a Module. It is more involved due to listobjects.
Sub Main()
Dim ws As Worksheet, rX As Range, rY As Range, rO As Range
Dim lO As ListObject, yLO As ListObject, xLO As ListObject
Dim y As Range
Application.DisplayAlerts = False
'Set ListOjbect("Independent")
For Each ws In Worksheets
For Each lO In ws.ListObjects
If lO.Name = "Independent" Then
Set yLO = lO
Exit For
End If
Next lO
If Not yLO Is Nothing Then Exit For
Next ws
If yLO Is Nothing Then GoTo EndSub
'Range for set of Y's
Set y = yLO.DataBodyRange.Columns(2).Resize(, yLO.DataBodyRange.Columns.Count - 1)
'Iterate dataset listobjects and regress
For Each ws In Worksheets
'Assume 1 ListObject per sheet
If ws.ListObjects.Count = 1 Then
If ws.ListObjects(1).Name <> "Independent" Then
Set xLO = ws.ListObjects(1)
Set rX = xLO.DataBodyRange.Columns(2).Resize(, xLO.DataBodyRange.Columns.Count - 1)
For Each rY In y.Columns
'Set range for regress output
Set rO = ws.Cells(1, LastNBCol(ws.Cells) + 2)
'Regress
Application.Run "ATPVBAEN.XLAM!Regress", rY, _
rX, False, False, , rO, _
False, False, False, False, , False
Next rY
End If
End If
Next ws
EndSub:
Application.DisplayAlerts = True
End Sub
Function LastNBCol(rng As Range) As Long
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = rng.Find(What:="*", after:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
LastNBCol = LastColumn
End Function
Reply With Quote
