VBA Data Analysis Loop Problem

[mark700]

Hi Everyone,

I have a macro which runs 25 uni-variate regression 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:

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

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.

 

[pgc01]

Hi
Welcome to the board

This code is equivalent to the one you posted. In each loop you simply add offsets to the ranges. Maybe this will help you to understand how to set up your loops.

Sub test()
Dim j As Long
 
For j = 0 To 2
 
    Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$D$1:$D$61").Offset(0, j), _
    ActiveSheet.Range("$AD$1:$AD$61").Offset(0, j), False, True, , ActiveSheet.Range( _
    "$D$129").Offset(20 * j, 0), False, False, False, False, , False
 
Next j
 
End Sub