Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Writing "Do Loop" Macros in Excel

Status
Not open for further replies.

ModManSEK

Aerospace
Nov 20, 2000
21
I'm trying to write a VB Macro that will minimize (i.e., drive to zero) a column array of function values. There are about sixty values in the array. To date, I've been manually doing each array value using either "Goal Seek" or "Solver". This would be okay (time-consuming but manageable) if I only had to do it a few times. But I find myself constantly tweaking the spreadsheet which means that I must redo the functions over and over again.

Does anyone know how to accomplish this in Excel VB? I'm new to VB so I'm having trouble getting the VB syntax correct. I recorded the steps into a macro but I can't seem to get it to march through the array. Thanks in advance.
 
Replies continue below

Recommended for you

from the built-in help:

This example shows how Do...Loop statements can be used. The inner Do...Loop statement loops 10 times, sets the value of the flag to False, and exits prematurely using the Exit Do statement. The outer loop exits immediately upon checking the value of the flag.

Dim Check, Counter
Check = True: Counter = 0 ' Initialize variables.
Do ' Outer loop.
Do While Counter < 20 ' Inner loop.
Counter = Counter + 1 ' Increment Counter.
If Counter = 10 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.
 
ModManSEK,
Suppose your data for each case is located in columns
First row is variable, the second row contain the formula you want to minimize. Here's the code:
Code:
Sub LoopGoalseek()
dim rngVar as range, rng0 as range
set rngVar = range(&quot;a1&quot;)
set rng0 = range(&quot;a2&quot;)

Do Until rngVar.value = &quot;&quot;
   rng0.GoalSeek Goal:=0, ChangingCell:=rngVar
   set rngVar = rngVar.offset(0,1)
   set rng0 = rng0.offset(0,1)
Loop

end sub
 
Much thanks to ivymike and yakpol on your responses! I ended up doing the following (which borrows -- quite liberally -- from both of your suggestions/posts):

Sub LoopGoalSeek()
'
' LoopGoalSeek Macro
' Macro recorded 4/2/2002 by Valued Gateway Client
'
' Keyboard Shortcut: Ctrl+x
'
Dim rngvar As Range, rng0 As Range, count As Integer
Set rngvar = Range(&quot;h13&quot;)
Set rng0 = Range(&quot;g13&quot;)
count = 0
For count = 1 To 60
rng0.GoalSeek Goal:=0, ChangingCell:=rngvar
Set rngvar = rngvar.Offset(1, 0)
Set rng0 = rng0.Offset(1, 0)
Next count
End Sub

My data are in vertical columns (starting at row 13 in columns G and H).

This could probably be improved by making it more flexible, etc., but it does the job for now. Again, thanks for the help!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor