Loop and GOAL seek

[DSLA]

Hi there! I have the following code, but it doesn't work. Any help is highly appreciated. I guess that the problem is that you can't loop with Set, but couldn't figure out any other way to do this.

Sub Makro1()
'
' Makro1 Makro
'

'
Dim Arvo As Range
Dim SelCell As Range
Range("E7").Select
Application.ScreenUpdating = False
Do
Set Arvo = ActiveCell.Offset(0, 1)
Set SelCell = ActiveCell.Offset(0, -1)
ActiveCell.GoalSeek Goal:=Arvo, ChangingCell:=SelCell
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, 1) = ""

End Sub

 

[WinteE]

Try this:

Sub Makro1()
'
' Makro1 Makro
'

'
Dim Arvo As Range
Dim SelCell As Range
Range("E7").Select
Application.ScreenUpdating = False
Do Until ActiveCell.Offset(0, 1) = ""
Set Arvo = ActiveCell.Offset(0, 1)
Set SelCell = ActiveCell.Offset(0, -1)
ActiveCell.GoalSeek Goal:=Arvo, ChangingCell:=SelCell
ActiveCell.Offset(1, 0).Select
Loop

End Sub

 

[p45cal]

You don't really need to set anything, try:

Sub blah()
For Each cll In Range(Range("E7"), Range("E7").End(xlDown)).Cells
    cll.GoalSeek Goal:=cll.Offset(, 1).Value, ChangingCell:=cll.Offset(, -1)
Next cll
End Sub

ps. your original code worked fine here.

Oops, small change:

Sub blah()
For Each cll In Range(Range("[COLOR=Red]F[/COLOR]7"), Range("[COLOR=Red]F[/COLOR]7").End(xlDown)).[COLOR=Red]Offset(, -1).[/COLOR]Cells
    cll.GoalSeek Goal:=cll.Offset(, 1).Value, ChangingCell:=cll.Offset(, -1)
Next cll
End Sub

 

[DSLA]

Thanks guys! Both solutions did the job perfectly.

 

[DSLA]

New problem occured. Instead of using Goalseek, I would like to use solver so that instead of =e7 I could find the biggest possible value that is <=e7. Can you help me with this?

 

[p45cal]

New problem occured. Instead of using Goalseek, I would like to use solver so that instead of =e7 I could find the biggest possible value that is <=e7. Can you help me with this?

I suspect this is a significantly more involved. I don't know whether I'll get around to it.
Quite important: what version of Excel are you using?

 

[DSLA]

Ok, I'm using Excel 2007.

 

[p45cal]

New problem occured. Instead of using Goalseek, I would like to use solver so that instead of =e7 I could find the biggest possible value that is <=e7. Can you help me with this?

I thought this would be awkward; below is a solution which works here. I've used the original macro (with commented out lines therein) and added a few more. There was a real problem getting solver to accept the range cll, for some reason tonly wanted to accept ranges in the form range("xxxx") instead of just simply cll, so I gave cll a name and used that.

Note that re:

find the biggest possible value that is <=e7

e7 was the cell we were changing - now you say it's the constraint?! You may have to change things around:

Sub blah()
'You need a reference to the Solver add-in in VBA.  First make sure that you've
'displayed the Solver Parameters dialog at least once in your Excel session.  Then
'open the Visual Basic Editor (Alt-F11), select Tools References, and check the
'box next to Solver.

Application.ScreenUpdating = False
For Each cll In Range(Range("F7"), Range("F7").End(xlDown)).Offset(, -1).Cells
    'cll.Select
    'cll.GoalSeek Goal:=cll.Offset(, 1).Value, ChangingCell:=cll.Offset(, -1)
    cll.Name = "ObjCell"
    SolverReset
    SolverOk SetCell:=Range("ObjCell"), MaxMinVal:=1, ValueOf:=0, ByChange:=cll.Offset(, -1), Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=Range("ObjCell"), Relation:=1, FormulaText:=cll.Offset(, 1).Value
    SolverSolve True
Next cll
Application.ScreenUpdating = True
End Sub