VBA loop

[Deuce Bucksman]

Hello, gurus.

This loop runs indefinitely. Any advice on how to stop it. This code works but it just goes on forever.


Sub macrotest()
Do Until IsEmpty(Cells)
For i = 2 To 6
Cells(4, i).GoalSeek goal:=Cells(6, i), changingcell:=Cells(3, i)
Next i
Loop
End Sub

 

[HalfAce]

Hi,
This is not tested, but if all else is working as desired, try just removing the lines:

Do Until IsEmpty(Cells)
[COLOR=Blue]and[/COLOR]
Loop

Does that help?

 

[Deuce Bucksman]

I'm not sure how to incorporate that change. Do I delete the For next (i)? Sorry, I appreciate your assistance but want to make sure I understand.

 

[HalfAce]

Oh, no it's more simple than that.
Just go into your code and (either) delete just those two lines (leaving what's between them in place) or you can put an apostrophe in front of those two lines, which will convert them from lines of (live) code to being just comments.
Your code should look like (either) this:

Sub macrotest()
[COLOR=Green]'Do Until IsEmpty(Cells)[/COLOR]
For i = 2 To 6
    Cells(4, i).GoalSeek goal:=Cells(6, i), changingcell:=Cells(3, i)
Next i
[COLOR=Green]'Loop[/COLOR]
End Sub

OR... this:

Sub macrotest()
For i = 2 To 6
    Cells(4, i).GoalSeek goal:=Cells(6, i), changingcell:=Cells(3, i)
Next i
End Sub

Either one will do what I'm suggesting.

That help?

 

[Biz]

Try

Sub macrotest()
    Dim i As Long
    On Error Resume Next
    For i = 2 To 6
        Cells(4, i).GoalSeek goal:=Cells(6, i), changingcell:=Cells(3, i)
    Next i
End Sub

Does it help?

Biz

 

[Deuce Bucksman]

Thanks, HalfAce and Biz. All codes work perfectly. Is a loop not possible with goal seek or is just not neccessary. I had to write some code today for a model an associate of mine is building, and he kept having to change the number of columns that he was trying to goal seek so I was looking for an easier way. Just curious. You both tackled the issue and the code works perfect! Thanks, again!

 

[Biz]

Thanks, HalfAce and Biz. All codes work perfectly. Is a loop not possible with goal seek or is just not neccessary. I had to write some code today for a model an associate of mine is building, and he kept having to change the number of columns that he was trying to goal seek so I was looking for an easier way. Just curious. You both tackled the issue and the code works perfect! Thanks, again!

Why not created Range Name?

1) SetCells
2) Tovalue
3) ChangingCells

This approach may work but not tested.


Biz

 

[HalfAce]

Is a loop not possible with goal seek or is just not necessary.

Yes, it's possible. The goalseek is currently being done within a loop in your existing code. (For i = 2 to 6... Next i)
The original code was running your existing loop within another loop (Do Until IsEmpty(Cells) ... Loop) that was saying "keep looping until all cells in the sheet are blank"
Of course all cells are not going to become blank. (hence the never ending loop.)

Does that make sense?

 

[Deuce Bucksman]

Aw, I now see where I went wrong. I was trying to make it so the loop would keep looping across until it encountered a cell with no content, but I can see how that would go south with another loop. Really appreciate your help. You saved me (and my counterpart) countless hours of monotonous goal-seeking.