VBA data analysis loop problem

[bobshah2010]

Hi there,

I'm after a macro that can loop through a data table that i've prepared so that Excel can run multiple regressions at the same time using the "Data Analysis Tool pack"

So current my data table is set up so that every dependent variable is right before the subsequent independent variables im trying to measure against. Take for example the 3 types of dependent variables im trying to measure are: Sales, Revenue and Cost.
My dependent variables are A, B all through to Z.

My Table is set up as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]Sales[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]Z[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]225[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

I would like the macro to cycle through this table from Left to Right and generate a Regression Analysis output for every Dependent variable (Sales, Revenue, Cost) - i.e generate a result for Sales with A and B as independent variables, then perform same output below for Revenue with A, B and D, Then beneath that output do the same for Cost with H, Z and again for Sales with A, H, Z etc. etc.

Currently this is the code I have written out so far to generate the table. Note that the Dependent Variable will always be fixed and for this example assume the Dependent variable will always be Sales, Revenue and Cost.

As i'm sure you are aware - one of the limitation with Excel is that it can only perform a regression on independent variables that are directly beside the dependent variable - which is why I had to create a macro to generate a table to assist excel in looping through and generating Regression analyses.

Sub RunRegression()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, s1 As Worksheet, lr As Long, lc As Integer
Dim c As Integer, c1 As Integer, r As Long, lc2 As Integer, rng As Range, lr2 As Long, lc3 As Integer, i As Long


Set ws1 = Sheets("Data Table")
Set ws2 = Sheets("Regression name List")
Set ws3 = Sheets("Regression Table")
Set s1 = Sheets("Regression Test")


s1.Range("M30:M1000").Copy ws2.Range("A1")


'Remove all blank cells


ws2.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


'clear regression table each run


Sheets("Regression Table").Cells.Clear


'create regression table from Data table to set up regression table to allow for regression to be peformed.


lr = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
lc = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
lc2 = ws3.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
For r = 1 To lr
    For c = 1 To lc
        If ws2.Cells(r, 1).Value = ws1.Cells(1, c).Value Then
            ws1.Columns(c).Copy
            ws3.Cells(1, lc2).PasteSpecial Paste:=xlPasteValues
           
        lc2 = lc2 + 1
        
        End If
        
    Next c
Next r


' Example of a for loop used to cycle through and perform regression


'    For i = 1 To 3
'
'        Application.Run "ATPVBAEN.XLAM!Regress", .Cells(1, .Column("C") + i).Resize(61), _
'        .Cells(1, .Column("AC") + i).Resize(61), False, True, , _
'        .Cells(109 + i * 20, .Column("C") + i), False, False, False, False, , False
'    Next i




End Sub

I've also included a snippet of something i found on the internet that shows someone using a for loop to cycle through a regression analysis. This may assist you in helping me find a solution for my problem.

 

[bobshah2010]

Bump anyone