VBA Data Analysis Loop Problem

[mark700]

Hi Everyone,

I have a macro which runs 25 uni-variate regressions using Excel's data analysis. However in writing this macro I manually changed all of the cell range values where the data is in my spreadsheet. I want to integrate a loop into my code in order to speed up the process, have a more efficient macro and be able to perform 200+ regressions multiple times. I do not know what type of loop I should use or how to address the ranges when compiling the loops. My code is as below:


[VBA]Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$D$1:$D$61"), _
ActiveSheet.Range("$AD$1:$AD$61"), False, True, , ActiveSheet.Range( _
"$D$129"), False, False, False, False, , False

Application.Run "ATPVBAEN.XLAM!Regress",
ActiveSheet.Range("$E$1:$E$61"), _
ActiveSheet.Range("$AE$1:$AE$61"), False, True, , ActiveSheet.Range( _
"$D$149"), False, False, False, False, , False

Application.Run "ATPVBAEN.XLAM!Regress",
ActiveSheet.Range("$F$1:$F$61"), _
ActiveSheet.Range("$AF$1:$AF$61"), False, True, , ActiveSheet.Range( _
"$D$169"), False, False, False, False, , False

[/VBA]
This cell range moves up one column each time and I want to be able to continue this trend multiple times. Any help would be much appreciated as I am unfamiliar with loops and new to VBA. Thanks very much for any help!

Regards,

D.

[Bob Phillips]

[vba]

With ActiveSheet

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 With[/vba]