Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Writing "Do Loop" Macros in Excel

Writing "Do Loop" Macros in Excel

Writing "Do Loop" Macros in Excel

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.

RE: Writing "Do Loop" Macros in Excel

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 Until Check = False ' Exit outer loop immediately.

RE: Writing "Do Loop" Macros in Excel

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:

Sub LoopGoalseek()
dim rngVar as range, rng0 as range
set rngVar = range("a1")
set rng0 = range("a2")

Do Until rngVar.value = ""
   rng0.GoalSeek Goal:=0, ChangingCell:=rngVar
   set rngVar = rngVar.offset(0,1)
   set rng0 = rng0.offset(0,1)

end sub

RE: Writing "Do Loop" Macros in Excel

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("h13")
Set rng0 = Range("g13")
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close