"DO" loop

E

enolregnar

This a macro that I recorded then added on to solve iteratively for th
value of Range("G4") that causes the calculated Range("F4") to b
zero:
' Macro recorded 11/13/2005 by Leon F Michon
'
' Keyboard Shortcut: Ctrl+b
'
Range("G4").Select
Range("G4").GoalSeek Goal:=0, ChangingCell:=Range("F4")
Range("G5").Select
Range("G5").GoalSeek Goal:=0, ChangingCell:=Range("F5")
Range("G6").Select
Range("G6").GoalSeek Goal:=0, ChangingCell:=Range("F6")
Range("G7").Select
Range("G7").GoalSeek Goal:=0, ChangingCell:=Range("F7")
Range("G8").Select
Range("G8").GoalSeek Goal:=0, ChangingCell:=Range("F8")
Range("G9").Select
Range("G9").GoalSeek Goal:=0, ChangingCell:=Range("F9")
Range("G10").Select
Range("G10").GoalSeek Goal:=0, ChangingCell:=Range("F10")
Range("G11").Select
Range("G11").GoalSeek Goal:=0, ChangingCell:=Range("F11")
I copied the original macro seven times and changed the row in eac
line to goal solve for rows 4 through 10. It's kind of like using
sledge hammer to drive a tack.

I'm thinking that there has to be a similar construct to the "do" loo
in FORTRAN. You would set up a For, Next loop:

Dim i As Integer
For i = 4,10
Range("G, i").Select
Range("G, i").GoalSeek Goal:=0, ChangingCell:=Range("F,i")
Next i
End Sub

How do I make the indexing variable for the loop to be the ro
address for Range ("Gi") and Range ("Fi"). Please forgive my speakin
VBA with a FORTRAN accent; I cut my teeth on FORTRAN back in the earl
60's
 
R

Rowan Drummond

Try:

Dim i As Long
For i = 4 to 10
Range("G" & i).GoalSeek Goal:=0, ChangingCell:=Range("F" & i)
Next i


Hope this helps
Rowan
 
D

Dana DeLouis

Just another option:

Sub Demo()
Dim Cell As Range
For Each Cell In Range("G4:G11").Cells
Cell.GoalSeek 0, Cell.Offset(0, -1)
Next Cell
End Sub

If you would like more precision, perhaps a Solver solution...

Sub Solver_Demo()
Dim Cell As Range
SolverOptions Precision:=0.000000001

For Each Cell In Range("G4:G11").Cells
SolverReset
SolverOk Cell.Address, 3, 0, Cell.Offset(0, -1).Address
SolverSolve True
Next Cell
End Sub

(Note: to use, start solver, and in the vba menu, do Tools | References..|
Select Solver )

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top