Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 10th, 2011, 03:59 PM
Registered User
Join Date: Jul 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Data Analysis Loop Problem

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!


Old July 10th, 2011, 09:57 PM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts

you can use the .Cells property of the worksheet like so:

Sub TryThis()

'Represents the column (A=1, B=2, etc)
Dim iColumn as Integer

With ActiveSheet
    For iColumn = 1 to 5 'step through columns A to E
        .Cells(iColumn , 1) = "This is column " & iColumn 
    Next iColumn 
End With

End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop to insert excel data into word userform cjcant Word VBA 18 July 21st, 2011 06:08 AM
Loop problem! Please help me!! olud ASP.NET 1.0 and 1.1 Basics 2 March 19th, 2007 07:38 AM
Data binding analysis of AJAX DataSet and ... kingstar SQL Language 0 December 19th, 2006 03:13 AM
Output loop data to a string iloveoatmeal Classic ASP Basics 2 December 22nd, 2005 08:28 PM
while loop problem shoakat Classic ASP Databases 1 September 21st, 2004 03:44 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.