Writing "Do Loop" Macros in Excel
Writing "Do Loop" Macros in Excel
(OP)
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.
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
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.
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)
Loop
end sub
RE: Writing "Do Loop" Macros in Excel
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!