Solved: Looping Goal Seek Function

[lukecj]

Hello all. I am pulling my hair out trying to figure out how to loop the goal seek function. The macro is incredibly straight forward and is shone here:

[vba]Sub Macro1()

Range("G2").GoalSeek Goal:=3, ChangingCell:=Range("F2")

End Sub[/vba]
How can I get this to loop so it calculates for each row in the attached excel spreadsheet? Thanks for the help.

Charles

[p45cal]

[VBA]Sub blah()
For Each cll In Range("G2:G7")
cll.GoalSeek Goal:=3, ChangingCell:=cll.Offset(, -1)
Next cll
End Sub
[/VBA]

[lukecj]

Thanks!

[F412aday]

I tried the above code but I cant get it to work. I am trying to use variable for the Range, goalseek goal, and cell offset values:

Private Sub cmdCalculate_Click()
Dim goalSpec As Variant
Dim changeSpec As Variant
Dim goalRange As Range

Worksheets("Sheet1").Activate

goalRange = Range(rfeInput1).Text
changeSpec = CInt(TextBox2)
goalSpec = CDbl(TextBox1)

For Each cll In Range(goalRange)
cll.GoalSeek goal:=goalSpec, ChangingCell:=cll.Offset(changeSpec, 0)
Next cll

End Sub

Please HELP!!!!!!!!!!! Thanks!

[p45cal]

Focus on goalrange which you've Dimmed as range.
Later you have:
goalRange = Range(rfeInput1).Text
which looks odd to me; Normally you'd have to use
Set goalRange = Range(rfeInput1).Text
but Range(rfeInput1).Text is unlikely to return a range object - perhaps it's meant to be:
goalRange = Range(rfeInput1.Text)
but that depends on what's in rfeInput.

[F412aday]

Thanks P45cal,

rfeInput is a row range of cell values (general format). This row of 6 cells are the "gola" for the goalseek routine using a row of 6 cells, 11 rows above, as the changing cells. I want to run goalseek for each of the 6 goalcells with one click. I tried changing

goalRange = Range(rfeInput1).Text to
Set goalRange = Range(rfeInput1.Text)

But I get the same Run-time error '1004': Method 'Range' of object '_Global' failed.

Could there be a problem with "cll' in the "For Each" command? This is not declared anywhere in my code and I am not sure if it is intrinsic to VBA or not. I have changed my code so no variable are used, but it still gives me "Run-time error '1004': Reference is not valid." on the line where goalseek is called:

[VBA]Private Sub cmdCalculate_Click()

Worksheets("Sheet1").Activate

For Each cll In Range("F18:K18")
cll.GoalSeek goal:=4, ChangingCell:=cll.Offset(-11, 0)
Next cll

End Sub[/VBA]

It would make my life a whole lot easier if this would work.... thanks for your help P45cal!!

[p45cal]

Typically, you'll get that error if there is no formula in cll.
Cll is a cell, being F18 to start with then being G18, then H18 etc. on each subsequent iteration of the loop. These cells should all contain formulae.

[F412aday]

It worked! Thanks a bunch!

[rtr.shazzad]

I tried the first code and this is really helpful. but i need to run a VBA in several rows to get goal seeked value.
here is my code-

Sub Runall()'
' Runall
'
' Keyboard Shortcut: Ctrl+K
'


For Each cll In Range("AX21:E21")
cll.GoalSeek Goal:=35, ChangingCell:=cll.Offset(-4, -1).Range( _
    "A1")
Next cll
    ActiveCell.Offset(4, 2).Range("A1").Select
    
End Sub

This VBA can be applied for the range AX21:E21. But i need to run this VBA for several rows like AX30:E30. I understand that an offset has to be built. but I am not yet a pro.

Any help from would be much appreciated.

Thanks a lot in advance.