Re: VBA code to run Goal Seek

Hi everyone,
Can I avoid typing 51 values into the Goal argument (in the GoalSeek function)?
In a recorded macro instead of .Range("B" & i).Value was there a numeric value..
Any help?

Dim i As Byte

With Worksheets("6.1New")

For i = 2 To 51

    .Range("G" & i).GoalSeek Goal:=.Range("B" & i).Value, _
    ChangingCell:=Range("F" & i)
    
Next i

End With

End Sub

Can you provide a short sample of sheet "6.1New", removing personal data, to help to understand the goal seek.

Sure, please see attached.

There are no formulas, so it is hard to fully understand what you are doing. My first thought on something like is to step away from Excel and visit the problem algebraically. If I can solve for column F algebraically, then I can simply put a formula in column F and completely bypass the use of Goal Seek. I cannot tell from the sample spreadsheet exactly what the algebra looks like. Does this look correct:

D=C-B
E=D/B
F=D/C (should this be negative?)

@MrShorty thanks: I'll switch back to Algebra then:
B=100
C=120
D=120-100=20
E=(120-100)/100=0.2=20%
F=The percentage value (negative) which makes C=B, in this case 17% [because 120-(120*17%)=100].
Hope it's clear now, and as far as I know you need to use SeekGoal in order to extract that value (17%). If I'm right, then again I'm looking for a way to feed GoalSeek in a loop (see my original post).

120-120*x=100 is easily solved for x (a quick algebra review: http://www.purplemath.com/modules/solvelin3.htm )

subtract 120 from both sides: -120*x=100-120=-20
divide by -120: x=-20/-120=1/6 or about 17%

well you must be right! However, for future generations who might wish to know if all arguments in the GoalSeek function can be indexed, so to be run from a loop.. a general say might be helpful here. To close the thread or leave it open?

In a generic sense, it is certainly possible to loop through goal seek. A lot of the particulars will depend on the details of each project. The vast majority of the time, it seems easier to solve the problem algebraically than it is to figure out how to loop through goal seek. That is why the algebraic solution is almost always the first one suggested. But, for those cases where it is not possible to solve it explicitly, Goal seek can be looped, or one can use any number of other root finding algorithms to obtain the desired solution.