VBA Data Analysis Loop Problem

M

mark700

New Member
Joined
Jul 10, 2011
Messages
1
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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.

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Replacing delete with a value
Replies
3
Views
186
Alex Blakenburg
A
C
  • Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
400
HighAndWilder
H
S
  • Question
vba logic to open up several files within a folder and perform 2-3 steps
Replies
1
Views
126
ranman256
ranman256
P
  • Solved
Counter in For Each loop
Replies
2
Views
207
Possum17
P
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
391
sspatriots
S

Forum statistics

Threads
1,215,373
Messages
6,124,562
Members
449,171
Latest member
jominadeo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top