Do Loop Goal Seek won't End Sub
 

I am just a novice & wrote a simple do loop for goal seeking. The goal seek
calculates however, the macro won't End Sub. The debugging highlights the
3rd to the last line below : Cells(81, i).GoalSeek Goal:=0,
ChangingCell:=Cells(74, i)



Sub GoalSeek()

'select/copy values
Range("AY79:BJ79").Copy
Range("AY76:BJ76").Select
Selection.PasteSpecial Paste:=xlValues


'set variables
Dim r As Integer, i As Integer
'assign column value to varibles
r = 50
i = 0

Cells(81, r).Select

Do
r = r + 1

Loop Until Cells(81, r) = "end"

For i = 51 To r

'goal seek
'set cell range (row 81) to 0 by adjusting 100% (row 74)
Cells(81, i).GoalSeek Goal:=0, ChangingCell:=Cells(74, i)

Next i

End Sub

Do Loop Goal Seek won't End Sub
 

There is nothing in your macro that would make Cells(81, r) = "end".

That is why your loop runs continuously. You could set it to loop until r =
456 which would be the last column of the sheet up throught XL2003. It goes
a lot higher than that in 2007.

"Sharon" wrote:

I am just a novice & wrote a simple do loop for goal seeking. The goal seek
calculates however, the macro won't End Sub. The debugging highlights the
3rd to the last line below : Cells(81, i).GoalSeek Goal:=0,
ChangingCell:=Cells(74, i)



Sub GoalSeek()

'select/copy values
Range("AY79:BJ79").Copy
Range("AY76:BJ76").Select
Selection.PasteSpecial Paste:=xlValues


'set variables
Dim r As Integer, i As Integer
'assign column value to varibles
r = 50
i = 0

Cells(81, r).Select

Do
r = r + 1

Loop Until Cells(81, r) = "end"

For i = 51 To r

'goal seek
'set cell range (row 81) to 0 by adjusting 100% (row 74)
Cells(81, i).GoalSeek Goal:=0, ChangingCell:=Cells(74, i)

Next i

End Sub

Do Loop Goal Seek won't End Sub
 

I think JLGWhiz meant 256 not 456!

the rest he sais is true.

cheers

carlo

On Nov 15, 1:18 pm, JLGWhiz wrote:
There is nothing in your macro that would make Cells(81, r) = "end".

That is why your loop runs continuously. You could set it to loop until r =
456 which would be the last column of the sheet up throught XL2003. It goes
a lot higher than that in 2007.



"Sharon" wrote:
I am just a novice & wrote a simple do loop for goal seeking. The goal seek
calculates however, the macro won't End Sub. The debugging highlights the
3rd to the last line below : Cells(81, i).GoalSeek Goal:=0,
ChangingCell:=Cells(74, i)

Sub GoalSeek()

'select/copy values
Range("AY79:BJ79").Copy
Range("AY76:BJ76").Select
Selection.PasteSpecial Paste:=xlValues

'set variables
Dim r As Integer, i As Integer
'assign column value to varibles
r = 50
i = 0

Cells(81, r).Select

Do
r = r + 1

Loop Until Cells(81, r) = "end"

For i = 51 To r

'goal seek
'set cell range (row 81) to 0 by adjusting 100% (row 74)
Cells(81, i).GoalSeek Goal:=0, ChangingCell:=Cells(74, i)

Next i

End Sub- Hide quoted text -

- Show quoted text -